工作常用
--报表：注意：AD_PI_ID对应数据字段 别忽略
-----执行计划
explain plan for sql_statement;
select * from table(dbms_xplan.display);
----后台自动任务(每五分钟运行一次)：
DECLARE
JOB_ID PLS_INTEGER;
BEGIN
SYS.DBMS_JOB.SUBMIT(JOB => JOB_ID,
WHAT => 'XQ_MIDSO_GEN;',
INTERVAL => 'sysdate + 5/(24*60)');
COMMIT;
END;
------------------------------------------------------------------------------------------
--自动获取单据编号示例：
v_table_id ad_table.id%TYPE; --采购退货单m_ret_pur表的id
v_m_ret_pur_docno m_ret_pur.docno%TYPE; ----采购退货单m_ret_pur单据编号
--获取表m_ret_pur的id
SELECT id
INTO v_table_id
FROM ad_table
WHERE NAME = upper('m_ret_pur');
--自动生成单据编号
SELECT t.sequencename
INTO v_m_ret_pur_docno
FROM ad_column t
WHERE t.ad_table_id = v_table_id
AND t.dbname = 'DOCNO';
v_m_ret_pur_docno := get_sequenceno(v_m_ret_pur_docno,
v.ad_client_id);
------------------------------------------------------------------------------------------
--查看某表或视图上的触发器
select * from all_triggers WHERE table_name=upper('m_in');
查看代码引用：
select * from user_source a where instr(lower(a.TEXT),'get_sequenceno') >= 1;
--扩展类常用于明细新增商品时，弹出界面，选择颜色尺寸和输入数量
nds.schema.AttributeDetailSupportTableImpl
获取序号生成器语句：
select * from ad_sequence where name='序号生成器名称';
--提交状态：字段翻译器
nds.web.alert.LimitValueAlerter
italic-purple-font-row
green-font-bold-row
---表的扩展属性排序示例---------------------------------
{"orderby":[{"column":"BILL_TYPE","desc":false},{"column":"NC_VOUCH_TEMPLETSET_ID","desc":false},{"column":"DOCNO","desc":false}
,{"column":"DEPFLAG", "asc":true},{"column":"NC_ACCSUBJ_ID", "asc":true}]}
---------------------------------------------------------------
------------------------------------------------------------------------------------
查找存储过程被哪些session锁住而无法编译
select * FROM dba_ddl_locks where name ='OPERATIONDATA_IMP';
--查看被锁的表
select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name ,b.OBJECT_ID,a.sid,a.SERIAL#
from v$process p,v$session a, v$locked_object b,all_objects c
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id-- and c.object_id=111579
order by p.SPID;
--查看数据库中的表锁（特定表被哪些session锁住）
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,
B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS,
C.SERVER,C.SID,C.SERIAL#,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
ORDER BY 1,2;
------------------------------------------------------------------------------------
---------------------------临时表无法修改解决------------------------------------
方法一：
select sid, serial#
from v$session
where sid in
(
select sid
from v$lock
where id1 =
(
select object_id
from user_objects
where object_name = upper('rp_o2o_salesources10')
)
);
alter system kill session '140,64317';
方法二：
select 'alter system kill session '||''''||a.SID||','||c.SERIAL#||''';',a.ID1,b.object_name
from v$lock a,dba_objects b,v$session c
where a.ID1=b.object_id and a.SID=c.SID and b.object_name='rp_o2o_salesources10';
alter system kill session 'SID,SERIAL#';
------------------------------------------------------
select count(*) from B_PAY where STATUS = 1 AND id=$OBJECTID$ AND ISACTIVE='Y'
drop index idx_
---------------------------------解析前台配置clob字段
v_sql VARCHAR2(4000);
v_sql1 VARCHAR2(4000);
r_store_ids r_tabid := r_tabid(); --门店id集合
v_sql := get_fitler_sql(v_marketid);
v_sql1 := 'select t.id from c_store t where t.id ' || v_sql;
r_store_ids := f_fast_table(v_sql1);
-----------------------------------------------------------
单对象界面按钮：动作定义内调用动作定义
v_mppp_id b_canso.m_product_pub_po_id%TYPE; --added by xy 20180728
v_query VARCHAR2(255); --added by xy 20180728
v_mppp_tabid ad_table.id%TYPE;--added by xy 20180728
--begin added by xy 20180728
--如果本单的宣传品征订单不为空，则：调用动作定义【撤单 M_PRODUCT_PUB_PO_CANCEL】
IF v_mppp_id IS NOT NULL THEN
SELECT id
INTO v_mppp_tabid
FROM ad_table
WHERE NAME = upper('m_product_pub_po');
v_query := '<id>' || v_mppp_id || '</id><query/><table>' ||
v_mppp_tabid || '</table>';
m_product_pub_po_cancel(p_user_id, v_query, r_code, r_message);
END IF;
--end added by xy 20180728
------------------------------------------------------------------------
---------------------------------------------trigger
CREATE OR REPLACE TRIGGER bi_m_product_pub
BEFORE UPDATE ON m_product_pub
FOR EACH ROW
DECLARE
----------------------------------------------
--author:xy
--date:20180727
--description:
--如果品种状态不为0或1，不允许修改商品编码。
--更新是否有尺寸要求、计量单位、备注、商品编码为最新的是否有尺寸要求、计量单位、备注、商品编码
----------------------------------------------
BEGIN
--如果品种状态不为0或1，不允许修改商品编码
IF (:new.typestatus <> 0 AND :new.typestatus <> 1) THEN
raise_application_error(-20001, '品种状态不为0或1，不允许修改商品编码！');
END IF;
--更新是否有尺寸要求、计量单位、备注、商品编码为最新的是否有尺寸要求、计量单位、备注、商品编码
UPDATE m_product_pub mpp
SET mpp.is_size = :new.is_size, mpp.units = :new.units,
mpp.description = :new.description, mpp.m_product_id = :new.m_product_id;
END;
---------------------------------------------
--AC模板
CREATE OR REPLACE PROCEDURE C_REBATE_ADJUST_AC(p_id IN NUMBER) AS
-------------------------------------------------------------------------
--History1.
--Author:xuyang
--Date:20180604
--Description:
--将库存表中店仓在头表所选的店仓内、款号上对应的供应商为头表所选的供应商、库存数量>0的数据插入到本单明细中
/*店仓：取符合条件的库存表的店仓
条码、商品、ASI：取符合条件的库存表里面的条码、商品、ASI
库存数量：取对应店仓、对应条码的库存数量
采购价：取对应款号档案的采购价
返利：取对应款号档案的固定返利
当期成本：取【商品成本】表中该款号的单品成本
调整成本：取采购价 - 返利
*/
-------------------------------------------------------------------------
begin
end;
-----------------------------------------------------
--提交存储过程模板
CREATE OR REPLACE PROCEDURE m_purchase_submit(p_submittedsheetid IN NUMBER,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
-------------------------------------------------------------------------
--1. Author:XY
--Date:20180728
--Description:
--
--
--
-------------------------------------------------------------------------
v_status number(1);
BEGIN
--并发控制，20120216 add by vetica
EXECUTE IMMEDIATE ('select 1 from table_name t where t.id=' ||
p_submittedsheetid || ' for update');
SELECT ad_client_id, status, modifierid, doctype, docno, p.b_po_id, p.isagt,
description, p.is_bas
INTO v_clientid, v_status, v_userid, v_doctype, v_docno, v_po_id, v_isagt,
v_description, v_is_bas
FROM m_purchase p
WHERE id = p_submittedsheetid;
IF v_status = 2 THEN
raise_application_error(-20201, '单据已提交，不能重复提交！');
END IF;
--更新提交标记，提交人，提交时间
UPDATE m_purchase t
SET t.status = 2, t.statuserid = v_userid, t.statustime = SYSDATE
WHERE t.id = p_submittedsheetid;
UPDATE m_purchaseitem
SET status = 2
WHERE m_purchase_id = p_submittedsheetid;
r_code := 0;
r_message := v_docno || '提交成功！！';
END;
--地素：薪资管理-》基本薪资管理（author：me）
CREATE OR REPLACE PROCEDURE C_BASIC_SALARY_UNSUBMIT(p_submittedsheetid IN NUMBER,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
-------------------------------------------------------------------------
--HISTORY:
--1.AUTHOR:XUYANG
-- DATE:20180601
-- DESCRIPTION: 如果单据未提交，不允许取消提交。
-- 更新单据状态为1、提交人为空、提交时间为空
-------------------------------------------------------------------------
v_status C_BASIC_SALARY.status%TYPE; --单据提交状态
v_docno C_BASIC_SALARY.docno%TYPE; --单据编号
BEGIN
--并发控制
BEGIN
EXECUTE IMMEDIATE 'select 1 from C_BASIC_SALARY t where t.id=' ||
p_submittedsheetid || ' for update nowait';
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20201, '单据正在取消提交,不能重复取消！');
END;
--如果单据未提交，不允许取消提交
SELECT cbs.status, cbs.docno
INTO v_status, v_docno
FROM C_BASIC_SALARY cbs
WHERE cbs.id = p_submittedsheetid;
IF v_status = 1 THEN
raise_application_error(-20201, '单据未提交，不允许取消提交！');
END IF;
-- 更新单据状态为1、提交人为空、提交时间为空
UPDATE C_BASIC_SALARY cbs
SET cbs.status = 1, cbs.statuserid = NULL, cbs.statustime = NULL
WHERE cbs.id = p_submittedsheetid;
r_code := 0;
r_message := v_docno || '取消提交成功！！';
END;
--奇客巴士：成本与月结->返利成本调整ac程序(author:me)：
CREATE OR REPLACE PROCEDURE c_rebate_adjust_ac(p_id IN NUMBER) AS
-------------------------------------------------------------------------
--History1.
--Author:xuyang
--Date:20180604
--Description:
--将库存表中店仓在头表所选的店仓内、款号上对应的供应商为头表所选的供应商、库存数量>0的数据插入到本单明细中
/*店仓：取符合条件的库存表的店仓
条码、商品、ASI：取符合条件的库存表里面的条码、商品、ASI
库存数量：取对应店仓、对应条码的库存数量
采购价：取对应款号档案的采购价
返利：取对应款号档案的固定返利
当期成本：取【商品成本】表中该款号的单品成本
调整成本：取采购价 - 返利
--注意:
(1)如果头表的供应商不为空，店仓不为空，则取库存表中店仓在头表所选的店仓内、款号上对应的供应商为头表所选的供应商、库存数量>0的数据插入到本单明细；
(2)如果头表的供应商不为空，店仓为空，则取库存表中、所有店仓下的、库存数量>0的、款号对应的供应商为头表所选供应商的、所有条码的数据插入到明细；
(3)如果头表的供应商为空、店仓不为空，则取所选店仓在库存表中、所有库存数量>0的所有条码的数据插入到明细；
(4)如果头表的供应商为空、店仓为空，则取库存表中、所有店仓下的、库存数量>0的所有条码的数据插入到明细
*/
-------------------------------------------------------------------------
v_ad_client_id c_rebate_adjust.ad_client_id%TYPE;
v_ad_org_id c_rebate_adjust.ad_org_id%TYPE;
v_ownerid c_rebate_adjust.ownerid%TYPE;
--v_modifierid C_REBATE_ADJUST.MODIFIERID%type;
--v_createdate C_REBATE_ADJUST.CREATIONDATE%type;
--v_modifydate C_REBATE_ADJUST.MODIFIEDDATE%type;
--v_cra_id c_rebate_adjust.id%TYPE;
--v_c_store_id c_store.id%type;
v_c_supplier_id c_rebate_adjust.c_supplier_id%TYPE;
/*用于获取所选店仓id集合*/
v_rebate_adjust c_rebate_adjust%ROWTYPE;
v_sql VARCHAR2(4000);
v_sql1 VARCHAR2(4000);
r_store r_tabid := r_tabid(); --店仓集合
v_c_store_filter c_rebate_adjust.c_store_filter%TYPE;
/*用于获取库存中满足条件(1)的数据记录*/
CURSOR cur_fa_storage1 IS
SELECT fas.m_product_id AS m_product_id, fas.c_store_id AS c_store_id,
fas.m_attributesetinstance_id AS m_attributesetinstance_id,
fas.qty AS qtystorage, fas.m_productalias_id AS m_productalias_id,
mp.precost AS precost, mp.fixrebate AS fixrebate,
fapc.percost AS precost_th,
(mp.precost - nvl(mp.fixrebate, 0)) AS precost_adj
FROM fa_storage fas
JOIN m_product mp
ON (fas.m_product_id = mp.id)
LEFT JOIN fa_product_cost fapc
ON (fapc.m_product_id = fas.m_product_id)
WHERE fas.qty > 0
AND mp.c_supplier_id = v_c_supplier_id
AND EXISTS (SELECT 1
FROM TABLE(r_store) t
WHERE t.id = fas.c_store_id);
TYPE list_table1 IS TABLE OF cur_fa_storage1%ROWTYPE INDEX BY BINARY_INTEGER;
type_list_table1 list_table1;
/*用于获取库存中满足条件(2)的数据记录*/
CURSOR cur_fa_storage2 IS
SELECT fas.m_product_id AS m_product_id, fas.c_store_id AS c_store_id,
fas.m_attributesetinstance_id AS m_attributesetinstance_id,
fas.qty AS qtystorage, fas.m_productalias_id AS m_productalias_id,
mp.precost AS precost, mp.fixrebate AS fixrebate,
fapc.percost AS precost_th,
(mp.precost - nvl(mp.fixrebate, 0)) AS precost_adj
FROM fa_storage fas
JOIN m_product mp
ON (fas.m_product_id = mp.id)
LEFT JOIN fa_product_cost fapc
ON (fapc.m_product_id = fas.m_product_id)
WHERE fas.qty > 0
AND mp.c_supplier_id = v_c_supplier_id;
TYPE list_table2 IS TABLE OF cur_fa_storage2%ROWTYPE INDEX BY BINARY_INTEGER;
type_list_table2 list_table2;
/*用于获取库存中满足条件(3)的数据记录*/
CURSOR cur_fa_storage3 IS
SELECT fas.m_product_id AS m_product_id, fas.c_store_id AS c_store_id,
fas.m_attributesetinstance_id AS m_attributesetinstance_id,
fas.qty AS qtystorage, fas.m_productalias_id AS m_productalias_id,
mp.precost AS precost, mp.fixrebate AS fixrebate,
fapc.percost AS precost_th,
(mp.precost - nvl(mp.fixrebate, 0)) AS precost_adj
FROM fa_storage fas
JOIN m_product mp
ON (fas.m_product_id = mp.id)
LEFT JOIN fa_product_cost fapc
ON (fapc.m_product_id = fas.m_product_id)
WHERE fas.qty > 0
AND EXISTS (SELECT 1
FROM TABLE(r_store) t
WHERE t.id = fas.c_store_id);
TYPE list_table3 IS TABLE OF cur_fa_storage3%ROWTYPE INDEX BY BINARY_INTEGER;
type_list_table3 list_table3;
/*用于获取库存中满足条件(4)的数据记录*/
CURSOR cur_fa_storage4 IS
SELECT fas.m_product_id AS m_product_id, fas.c_store_id AS c_store_id,
fas.m_attributesetinstance_id AS m_attributesetinstance_id,
fas.qty AS qtystorage, fas.m_productalias_id AS m_productalias_id,
mp.precost AS precost, mp.fixrebate AS fixrebate,
fapc.percost AS precost_th,
(mp.precost - nvl(mp.fixrebate, 0)) AS precost_adj
FROM fa_storage fas
JOIN m_product mp
ON (fas.m_product_id = mp.id)
LEFT JOIN fa_product_cost fapc
ON (fapc.m_product_id = fas.m_product_id)
WHERE fas.qty > 0;
TYPE list_table4 IS TABLE OF cur_fa_storage4%ROWTYPE INDEX BY BINARY_INTEGER;
type_list_table4 list_table4;
BEGIN
--获取相关数据
SELECT cra.ad_client_id, cra.ad_org_id, cra.ownerid, cra.c_supplier_id,
cra.c_store_filter
INTO v_ad_client_id，v_ad_org_id，v_ownerid, v_c_supplier_id, v_c_store_filter
FROM c_rebate_adjust cra
WHERE cra.id = p_id;
--获取所选店仓集合
IF v_c_store_filter IS NOT NULL THEN
SELECT *
INTO v_rebate_adjust
FROM c_rebate_adjust cra
WHERE cra.id = p_id;
v_sql := get_fitler_sql(v_rebate_adjust.c_store_filter);
v_sql1 := 'select t.id from c_store t where t.id ' || v_sql;
r_store := f_fast_table(v_sql1);
END IF;
--将库存表中店仓在头表所选的店仓内、款号上对应的供应商为头表所选的供应商、库存数量>0的数据插入到本单明细中
IF v_c_supplier_id IS NOT NULL AND r_store.count > 0 THEN
BEGIN
OPEN cur_fa_storage1;
FETCH cur_fa_storage1 BULK COLLECT
INTO type_list_table1;
FORALL idx IN 1 .. type_list_table1.count
INSERT INTO c_rebate_adjustitem crai
(crai.id, crai.ad_client_id, crai.ad_org_id, crai.ownerid,
crai.modifierid, crai.creationdate, crai.modifieddate,
crai.c_rebate_adjust_id, crai.c_store_id,
crai.m_productalias_id, crai.m_product_id, crai.qtystorage,
crai.precost, crai.fixrebate, crai.precost_ths,
crai.precost_adj, crai.m_attributesetinstance_id)
VALUES
(get_sequences('C_REBATE_ADJUSTITEM'), v_ad_client_id,
v_ad_org_id, v_ownerid, NULL, SYSDATE, NULL, p_id,
type_list_table1(idx).c_store_id,
type_list_table1(idx).m_productalias_id,
type_list_table1(idx).m_product_id,
type_list_table1(idx).qtystorage,
type_list_table1(idx).precost,
type_list_table1(idx).fixrebate,
type_list_table1(idx).precost_th,
type_list_table1(idx).precost_adj,
type_list_table1(idx).m_attributesetinstance_id);
CLOSE cur_fa_storage1;
END;
ELSIF v_c_supplier_id IS NOT NULL AND r_store.count = 0 THEN
--如果头表的供应商不为空，店仓为空，则取库存表中、所有店仓下的、库存数量>0的、款号对应的供应商为头表所选供应商的、所有条码的数据插入到明细
BEGIN
OPEN cur_fa_storage2;
FETCH cur_fa_storage2 BULK COLLECT
INTO type_list_table2;
FORALL idx IN 1 .. type_list_table2.count
INSERT INTO c_rebate_adjustitem crai
(crai.id, crai.ad_client_id, crai.ad_org_id, crai.ownerid,
crai.modifierid, crai.creationdate, crai.modifieddate,
crai.c_rebate_adjust_id, crai.c_store_id,
crai.m_productalias_id, crai.m_product_id, crai.qtystorage,
crai.precost, crai.fixrebate, crai.precost_ths,
crai.precost_adj, crai.m_attributesetinstance_id)
VALUES
(get_sequences('C_REBATE_ADJUSTITEM'), v_ad_client_id,
v_ad_org_id, v_ownerid, NULL, SYSDATE, NULL, p_id,
type_list_table2(idx).c_store_id,
type_list_table2(idx).m_productalias_id,
type_list_table2(idx).m_product_id,
type_list_table2(idx).qtystorage,
type_list_table2(idx).precost,
type_list_table2(idx).fixrebate,
type_list_table2(idx).precost_th,
type_list_table2(idx).precost_adj,
type_list_table2(idx).m_attributesetinstance_id);
CLOSE cur_fa_storage2;
END;
ELSIF nvl(v_c_supplier_id, 0) = 0 AND r_store.count > 0 THEN
--如果头表的供应商为空、店仓不为空，则取所选店仓在库存表中、所有库存数量>0的所有条码的数据插入到明细；
BEGIN
OPEN cur_fa_storage3;
FETCH cur_fa_storage3 BULK COLLECT
INTO type_list_table3;
FORALL idx IN 1 .. type_list_table3.count
INSERT INTO c_rebate_adjustitem crai
(crai.id, crai.ad_client_id, crai.ad_org_id, crai.ownerid,
crai.modifierid, crai.creationdate, crai.modifieddate,
crai.c_rebate_adjust_id, crai.c_store_id,
crai.m_productalias_id, crai.m_product_id, crai.qtystorage,
crai.precost, crai.fixrebate, crai.precost_ths,
crai.precost_adj, crai.m_attributesetinstance_id)
VALUES
(get_sequences('C_REBATE_ADJUSTITEM'), v_ad_client_id,
v_ad_org_id, v_ownerid, NULL, SYSDATE, NULL, p_id,
type_list_table3(idx).c_store_id,
type_list_table3(idx).m_productalias_id,
type_list_table3(idx).m_product_id,
type_list_table3(idx).qtystorage,
type_list_table3(idx).precost,
type_list_table3(idx).fixrebate,
type_list_table3(idx).precost_th,
type_list_table3(idx).precost_adj,
type_list_table3(idx).m_attributesetinstance_id);
CLOSE cur_fa_storage3;
END;
ELSE
--如果头表的供应商为空、店仓为空，则取库存表中、所有店仓下的、库存数量>0的所有条码的数据插入到明细
BEGIN
OPEN cur_fa_storage4;
FETCH cur_fa_storage4 BULK COLLECT
INTO type_list_table4;
FORALL idx IN 1 .. type_list_table4.count
INSERT INTO c_rebate_adjustitem
(id, ad_client_id, ad_org_id, ownerid, modifierid,
creationdate, modifieddate, c_rebate_adjust_id, c_store_id,
m_productalias_id, m_product_id, qtystorage, precost,
fixrebate, precost_ths, precost_adj,
m_attributesetinstance_id, isactive)
VALUES
(get_sequences('C_REBATE_ADJUSTITEM'), v_ad_client_id,
v_ad_org_id, v_ownerid, NULL, SYSDATE, NULL, p_id,
type_list_table4(idx).c_store_id,
type_list_table4(idx).m_productalias_id,
type_list_table4(idx).m_product_id,
type_list_table4(idx).qtystorage,
type_list_table4(idx).precost,
type_list_table4(idx).fixrebate,
type_list_table4(idx).precost_th,
type_list_table4(idx).precost_adj,
type_list_table4(idx).m_attributesetinstance_id, 'Y');
CLOSE cur_fa_storage4;
END;
END IF;
END;
----奇客巴士：成本与月结->返利成本调整提交程序(author:me)
CREATE OR REPLACE PROCEDURE c_rebate_adjust_submit(p_submittedsheetid IN NUMBER,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
-------------------------------------------------------------------------
--History:
--1. Author:xuyang
--Date:2018-06-05
--增加控制:如果单据已经提交，不允许再次提交
--增加控制:如果明细中同一个商品存在调整成本不一致的记录，不允许！（提示：商品：XXX，在明细中的调整成本不一致，不允许！）
--增加控制:将本单数据生成一张已提交的成本调整单:
/*
单据编号：自动生成。
单据日期：返利成本调整单的单据日期。
经销商：取经销商级别为‘总部’的经销商档案的ID。
年月：返利成本调整单的单据日期对应的年月。
备注：由返利成本调整单：XXX 提交生成！
*/
--增加控制:将本单明细的数据按照商品汇总，生成成本调整单明细:
/*
商品：返利成本调整单明细的商品。
单品成本：取明细中该商品的调整成本（取平均值，不能取合计值）
*/
--更新单据状态为2、提交人为单据的修改人、提交时间为SYSDATE。
--
-------------------------------------------------------------------------
v_status c_rebate_adjust.status%TYPE; --单据提交状态
v_docno c_rebate_adjust.docno%TYPE; --单据号
v_submituserid c_rebate_adjust.modifierid%TYPE; --提交人为修改人
v_item_cnt NUMBER(10); --明细数量
v_precost_adj c_rebate_adjustitem.precost_adj%TYPE; --调整价
v_billdate c_rebate_adjust.billdate%TYPE; --单据日期
v_m_product_name m_product.name%TYPE; --款号名
v_ownerid c_rebate_adjust.ownerid%TYPE;
v_ad_org_id c_rebate_adjust.ad_org_id%TYPE;
v_ad_client_id c_rebate_adjust.ad_client_id%TYPE;
v_cost_adj_docno fa_costinitial.docno%TYPE; --成本调整单据编号
v_description fa_costinitial.description%TYPE; --成本调整单备注
v_c_customer_id fa_costinitial.c_customer_id%TYPE; --成本调整单经销商id
v_fa_costinitial_id fa_costinitial.id%TYPE;
v_table_id ad_table.id%TYPE; --表fa_costinitial的id
v_norepeat_cnt NUMBER(10); --返利成本调整单明细商品数（不重复）
BEGIN
--并发控制
EXECUTE IMMEDIATE ('select 1 from c_rebate_adjust t where t.id=' ||
p_submittedsheetid || ' for update');
--如果单据已经提交，不允许再次提交
SELECT cra.status, cra.docno, cra.modifierid, cra.billdate, ad_client_id,
ad_org_id, ownerid
INTO v_status, v_docno, v_submituserid, v_billdate, v_ad_client_id,
v_ad_org_id, v_ownerid
FROM c_rebate_adjust cra
WHERE cra.id = p_submittedsheetid;
IF v_status = 2 THEN
raise_application_error(-20201, '单据已提交，不允许重复提交！');
END IF;
--获取明细数量
SELECT COUNT(1)
INTO v_item_cnt
FROM c_rebate_adjustitem crai
WHERE crai.c_rebate_adjust_id = p_submittedsheetid;
--如果明细中同一个商品存在调整成本不一致的记录，不允许！（提示：商品：XXX，在明细中的调整成本不一致，不允许！）
IF v_item_cnt >= 2 THEN
FOR v_list IN (SELECT crai.id, crai.m_product_id,
nvl(crai.precost_adj, 0) AS precost_adj
FROM c_rebate_adjustitem crai
WHERE crai.c_rebate_adjust_id = p_submittedsheetid) LOOP
SELECT nvl(crai1.precost_adj, 0) AS precost_adj
INTO v_precost_adj
FROM c_rebate_adjustitem crai1
WHERE crai1.id <> v_list.id
AND crai1.m_product_id = v_list.m_product_id
AND rownum <= 1;
IF v_list.precost_adj <> v_precost_adj THEN
SELECT mp.name
INTO v_m_product_name
FROM m_product mp
WHERE mp.id = v_list.m_product_id;
raise_application_error(-20201,
'商品：' || v_m_product_name ||
',在明细中的调整成本不一致，不允许!');
END IF;
END LOOP;
END IF;
--增加控制:将本单数据生成一张已提交的成本调整单
/*
单据编号：自动生成。
单据日期：返利成本调整单的单据日期。
经销商：取经销商级别为‘总部’的经销商档案的ID。
年月：返利成本调整单的单据日期对应的年月。
备注：由返利成本调整单：XXX 提交生成!
*/
v_fa_costinitial_id := get_sequences('fa_costinitial');
--获取表fa_costinitial的id
SELECT id
INTO v_table_id
FROM ad_table
WHERE NAME = upper('fa_costinitial');
--自动生成单据编号
SELECT t.sequencename
INTO v_cost_adj_docno
FROM ad_column t
WHERE t.ad_table_id = v_table_id
AND t.dbname = 'DOCNO';
v_cost_adj_docno := get_sequenceno(v_cost_adj_docno, v_ad_client_id);
v_description := '由返利成本调整单：' || v_docno || '提交生成!';
--获取明细商品非重复数量
SELECT COUNT(DISTINCT(m_product_id))
INTO v_norepeat_cnt
FROM c_rebate_adjustitem crai
WHERE crai.c_rebate_adjust_id = p_submittedsheetid;
--取经销商级别为‘总部’的经销商档案的ID
SELECT cc.id
INTO v_c_customer_id
FROM c_customer cc
JOIN c_cusrank ccr
ON (cc.c_cusrank_id = ccr.id)
WHERE ccr.name = '总部';
INSERT INTO fa_costinitial fac
(id, ad_client_id, ad_org_id, ownerid, modifierid, creationdate,
modifieddate, isactive, docno, doctype, billdate, description, status,
au_state, au_pi_id, tot_lines, c_period_id, submitdate, statuserid,
statustime, c_customer_id, yearmonth)
VALUES
(v_fa_costinitial_id, v_ad_client_id, v_ad_org_id, v_ownerid,
v_submituserid, SYSDATE, SYSDATE, 'Y', v_cost_adj_docno, NULL,
v_billdate, v_description, 1, NULL, NULL, v_norepeat_cnt, NULL, NULL,
NULL, NULL, v_c_customer_id, substr(v_billdate, 1, 6));
--增加控制:将本单明细的数据按照商品汇总，生成成本调整单明细:
/*
商品：返利成本调整单明细的商品。
单品成本：取明细中该商品的调整成本（取平均值，不能取合计值）
*/
FOR v_list1 IN (SELECT m_product_id, AVG(precost_adj) AS precost_adj
FROM c_rebate_adjustitem
WHERE c_rebate_adjust_id = p_submittedsheetid
GROUP BY m_product_id) LOOP
INSERT INTO fa_costinitialitem faci
(id, ad_client_id, ad_org_id, ownerid, modifierid, creationdate,
modifieddate, isactive, fa_costinitial_id, m_product_id, percost,
status)
VALUES
(get_sequences('fa_costinitialitem'), v_ad_client_id, v_ad_org_id,
v_ownerid, v_submituserid, SYSDATE, NULL, 'Y', v_fa_costinitial_id,
v_list1.m_product_id, v_list1.precost_adj, 1);
END LOOP;
-- 更新单据状态为2、提交人为单据的修改人、提交时间为SYSDATE
UPDATE c_rebate_adjust cra
SET cra.status = 2, cra.statuserid = v_submituserid,
cra.statustime = SYSDATE
WHERE cra.id = p_submittedsheetid;
r_code := 0;
r_message := v_docno || '提交成功！！';
END;
--新骏：订单总汇报表示例（author:others）：
CREATE OR REPLACE PROCEDURE rp_posum_gen(p_pi_id NUMBER) IS
--Author:Arwen
-- 1. Date:20180521
-- Author :Arwen
-- Modification :
/*新增报表模板【订单总汇报表】，类型：统计报表，类别：财务报表，事实表：RP_POSUM，
预计算程序：RP_POSUM_GEN，具体逻辑：
查询条件：单据日期（日期范围、必填） 、商品（关联款号档案，非必填）、
供应商（外键关联供应商档案，多选，非必填）、采购订单（外键关联采购订单，多选，非必填）、
店仓（外键关联店仓档案，多选，非必填）、工厂交期（日期范围、必填）、客人交期（日期范围、必填）
查询逻辑： 取单据日期在查询条件日期范围内、商品在查询条件的商品内、
供应商在查询条件的供应商内、采购订单在查询条件的采购订单内、
采购店仓在查询条件的店仓内、已提交、可用的【采购订单】的数据。*/
-- 2. Date:20180607
-- Author :xuyang
-- Modification :修改汇总字段: 运费和毛利
/* 运费：
原逻辑：取符合条件的销售单对应的销售核价单表头的【运费】字段（如果没有核价单，运费就取0）
现逻辑：取符合条件的销售单对应的销售核价单明细的【运费】字段（如果没有核价单，运费就取0
毛利：
原逻辑：卖价-买价。
现逻辑：卖价-买价-运费
*/
v_sql_0 VARCHAR(4000); --原始SQL
v_modifierid ad_pinstance.modifierid%TYPE; --修改人
v_ad_client_id ad_pinstance.ad_client_id%TYPE; --所属公司
v_ad_org_id ad_pinstance.ad_org_id%TYPE; --所属组织
v_date LONG; --日期
v_datebegin NUMBER(8); --单据日期开始
v_dateend NUMBER(8); --单据日期结束
v_date2 LONG; --日期
v_datebegin2 NUMBER(8); --工厂日期开始
v_dateend2 NUMBER(8); --工厂日期结束
v_date3 LONG; --日期
v_datebegin3 NUMBER(8); --客人日期开始
v_dateend3 NUMBER(8); --客人日期结束
r_c_supplier_id r_tabid; --供应商检索
r_b_po_id r_tabid; --采购订单检索
r_m_product r_tabid; --款号检索
r_c_store r_tabid; --店检索
v_sql_1 LONG; --检索条件1
v_sql_2 LONG; --检索条件2
v_sql_3 LONG; --检索条件3
v_sql_4 LONG;
BEGIN
EXECUTE IMMEDIATE ('truncate TABLE RP_POSUM');
--raise_application_error(-20201, p_pi_id);
/*获取检索条件*/
v_sql_0 := 'select t.info from ad_pinstance_para t
where t.name=:name and t.ad_pinstance_id=:pid';
SELECT t.modifierid, t.ad_client_id, t.ad_org_id
INTO v_modifierid, v_ad_client_id, v_ad_org_id
FROM ad_pinstance t
WHERE t.id = p_pi_id;
--供应商集合
EXECUTE IMMEDIATE v_sql_0
INTO v_sql_1
USING 'SUP', p_pi_id;
r_c_supplier_id := f_fast_table(v_sql_1);
--采购订单
EXECUTE IMMEDIATE v_sql_0
INTO v_sql_2
USING 'DOCNO', p_pi_id;
r_b_po_id := f_fast_table(v_sql_2);
--款号
EXECUTE IMMEDIATE v_sql_0
INTO v_sql_3
USING 'PRODUCT', p_pi_id;
r_m_product := f_fast_table(v_sql_3);
--店仓
EXECUTE IMMEDIATE v_sql_0
INTO v_sql_4
USING 'C_STORE', p_pi_id;
r_c_store := f_fast_table(v_sql_4);
--单据日期
SELECT t.info
INTO v_date
FROM ad_pinstance_para t
WHERE t.name = 'BILLDATE'
AND t.ad_pinstance_id = p_pi_id;
IF v_date IS NULL THEN
--如开始日期和结束日期都没有选择，则查询所有日期范围内数据
v_datebegin := 0;
v_dateend := 20991231;
ELSE
IF instr(v_date, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为20991231
v_dateend := 20991231;
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin
FROM dual;
ELSIF instr(v_date, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为0
v_datebegin := 0;
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend
FROM dual;
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取
SELECT substr(v_date, 21, 8), substr(v_date, 34, 8) --注意这里是不是分别从17,30开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin, v_dateend
FROM dual;
END IF;
END IF;
--工厂交期
SELECT t.info
INTO v_date2
FROM ad_pinstance_para t
WHERE t.name = 'GCJQDATE'
AND t.ad_pinstance_id = p_pi_id;
IF v_date2 IS NULL THEN
--如开始日期和结束日期都没有选择，则查询所有日期范围内数据
v_datebegin2 := 0;
v_dateend2 := 20991231;
ELSE
IF instr(v_date2, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为20991231
v_dateend2 := 20991231;
SELECT substr(v_date2, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin2
FROM dual;
ELSIF instr(v_date2, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为0
v_datebegin2 := 0;
SELECT substr(v_date2, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend2
FROM dual;
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取
SELECT substr(v_date2, 21, 8), substr(v_date2, 34, 8) --注意这里是不是分别从17,30开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin2, v_dateend2
FROM dual;
END IF;
END IF;
--客人交期
SELECT t.info
INTO v_date3
FROM ad_pinstance_para t
WHERE t.name = 'KRJQDATE'
AND t.ad_pinstance_id = p_pi_id;
IF v_date3 IS NULL THEN
--如开始日期和结束日期都没有选择，则查询所有日期范围内数据
v_datebegin3 := 0;
v_dateend3 := 20991231;
ELSE
IF instr(v_date3, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为20991231
v_dateend3 := 20991231;
SELECT substr(v_date3, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin3
FROM dual;
ELSIF instr(v_date3, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为0
v_datebegin3 := 0;
SELECT substr(v_date3, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend3
FROM dual;
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取
SELECT substr(v_date3, 21, 8), substr(v_date3, 34, 8) --注意这里是不是分别从17,30开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin3, v_dateend3
FROM dual;
END IF;
END IF;
/* 原逻辑：取符合条件的销售单对应的销售核价单表头的【运费】字段（如果没有核价单，运费就取0）
现逻辑：取符合条件的销售单对应的销售核价单明细的【运费】字段（如果没有核价单，运费就取0*/
--插入销售值
INSERT INTO rp_posum
(id, ad_client_id, ad_org_id, m_product_id, sa_contractno,
c_customer_id, pdtno, pz, contractno, contractpno, confirmdate,
factorydate, priceactual, amt_tran, sa_qty, sa_qtyout, sa_amtout,
m_attributesetinstance_id, ownerid, creationdate, modifierid,
modifieddate, isactive, ad_pi_id, m_productalias_id, pu_contractno)
SELECT get_sequences('rp_posum'), 37, 27, b.m_product_id,
/* a.contractno_orig*/ a.old_docno, a.c_customer_id, a.pdtno,
a.pz, a.contractno, a.contractpno, a.confirmdate, a.factorydate,
b.priceactual, AVG(nvl(b.amt_tran, 0)), SUM(b.qty), SUM(b.qtyout),
SUM(b.tot_amtout_actual), b.m_attributesetinstance_id,
v_modifierid, SYSDATE, v_modifierid, SYSDATE, 'Y', p_pi_id,
b.m_productalias_id, b.contractno_orig
FROM m_sale a, m_saleitem b, TABLE(r_c_store) rc, TABLE(r_m_product) rp
WHERE a.id = b.m_sale_id
AND b.contractno_orig IS NOT NULL
AND a.status = 2
AND a.isactive = 'Y'
AND a.c_store_id = rc.id
AND b.m_product_id = rp.id
AND a.billdate BETWEEN v_datebegin AND v_dateend
AND a.factorydate BETWEEN v_datebegin2 AND v_dateend2
AND a.confirmdate BETWEEN v_datebegin3 AND v_dateend3
GROUP BY b.m_product_id, /*a.contractno_orig*/ a.old_docno,
a.c_customer_id, a.pdtno, a.pz, a.contractno, a.contractpno,
a.confirmdate, a.factorydate, b.priceactual,
b.m_attributesetinstance_id, b.m_productalias_id,
b.contractno_orig;
--插入采购值
MERGE INTO rp_posum m
USING (SELECT a.id, a.docno, a.billdate, a.old_docno AS contractno,
a.c_supplier_id, b.m_product_id, b.m_productalias_id,
b.m_attributesetinstance_id AS asi,
AVG(b.priceactual) AS po_priceactual, SUM(b.qty) AS po_qty,
SUM(b.qtyconsign) AS po_qtyin
FROM b_po a, b_poitem b, TABLE(r_b_po_id) bp, TABLE(r_c_store) rc,
TABLE(r_m_product) rp, TABLE(r_c_supplier_id) rs
WHERE a.id = b.b_po_id
AND a.id = bp.id
AND a.c_supplier_id = rs.id
AND a.billdate BETWEEN v_datebegin AND v_dateend
AND a.c_store_id = rc.id
AND b.m_product_id = rp.id
GROUP BY a.id, a.docno, a.billdate, a.old_docno, a.c_supplier_id,
b.m_product_id, b.m_productalias_id,
b.m_attributesetinstance_id) n
ON (m.pu_contractno = n.contractno AND m.m_productalias_id = n.m_productalias_id)
WHEN MATCHED THEN
UPDATE
SET m.c_supplier_id = n.c_supplier_id, m.billdate = n.billdate,
m.po_priceactual = n.po_priceactual, m.po_qty = n.po_qty,
m.po_qtyin = n.po_qtyin, m.po_amtin = n.po_qtyin * n.po_priceactual,
m.b_po_id = n.id
WHERE m.ad_pi_id = p_pi_id
WHEN NOT MATCHED THEN
INSERT
(id, ad_client_id, ad_org_id, m_product_id, c_supplier_id, billdate,
pu_contractno, po_priceactual, po_qty, po_qtyin, po_amtin,
m_attributesetinstance_id, ownerid, creationdate, modifierid,
modifieddate, isactive, ad_pi_id, m_productalias_id, b_po_id)
VALUES
(get_sequences('rp_posum'), 37, 27, n.m_product_id, n.c_supplier_id,
n.billdate, n.contractno, n.po_priceactual, n.po_qty, n.po_qtyin,
n.po_qtyin * n.po_priceactual, n.asi, v_modifierid, SYSDATE,
v_modifierid, SYSDATE, 'Y', p_pi_id, n.m_productalias_id, n.id);
--入库日期更新
FOR v IN (SELECT a.b_po_id, MIN(a.datein) AS datein
FROM m_purchase a
WHERE EXISTS (SELECT 1
FROM rp_posum b
WHERE a.b_po_id = b.b_po_id
AND b.b_po_id IS NOT NULL
AND b.ad_pi_id = p_pi_id)
AND a.datein IS NOT NULL
GROUP BY a.b_po_id) LOOP
UPDATE rp_posum t
SET t.datein = v.datein
WHERE t.b_po_id = v.b_po_id
AND t.ad_pi_id = p_pi_id;
END LOOP;
--毛利计算
/*
毛利：
原逻辑：卖价-买价。
现逻辑：卖价-买价-运费
*/
UPDATE rp_posum t
SET t.profit = t.priceactual - t.po_priceactual - t.amt_tran,
t.diff_qty = t.po_qty - t.sa_qty, t.inventory = t.po_qtyin - t.sa_qtyout
WHERE t.ad_pi_id = p_pi_id;
--raise_application_error(-20201, v_datebegin3);
END;
--新骏：采购核价单-》平摊运费（动作定义）存储过程（author：me）
CREATE OR REPLACE PROCEDURE m_puramttran_avg(p_user_id IN NUMBER,
p_query IN VARCHAR2,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
---------------------------------------------------------
--author: xuyang
--date: 20180607
/*增加控制：如果单据已提交，不允许。
如果头表的运费不为空，则：
更新明细的运费=头表的运费*明细的审核金额/头表的总审核金额（最后多出来的放在最后一行）
*/
---------------------------------------------------------
--声明用于从p_query解析参数获得单据ID的相关记录和变量
TYPE t_queryobj IS RECORD(
"table" VARCHAR2(255),
query VARCHAR2(32676),
id VARCHAR2(10));
v_queryobj t_queryobj;
TYPE t_selection IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
v_selection t_selection;
st_xml VARCHAR2(32676);
v_xml xmltype;
p_id NUMBER(10); --单据ID
--其他变量定义
v_status m_pur_price.status%TYPE; --单据状态
v_amt_tran m_pur_price.amt_tran%TYPE; --单据运费
v_docno m_pur_price.docno%TYPE; --单据编号
v_tot_amtin_pcheck m_pur_price.tot_amtin_pcheck%TYPE; --总审核金额
v_avg_amt_tran m_pur_price.amt_tran%TYPE; ----平摊运费
v_mod_amt_tran m_pur_price.amt_tran%TYPE; --剩余运费
v_m_purchase_id m_purchase.id%TYPE; --所属采购单ID
/*声明用于获取本单据明细中数量及存储各明细记录ID*/
TYPE type_list IS TABLE OF m_purchase.id%TYPE INDEX BY BINARY_INTEGER;
v_list type_list;
v_cnt NUMBER(10); --明细中行数
--用于获取明细数量的游标
CURSOR cur_list IS
SELECT id
FROM m_pur_priceitem
WHERE m_purchase_id = v_m_purchase_id;
BEGIN
-- 从p_query解析参数
st_xml := '<data>' || p_query || '</data>';
v_xml := xmltype(st_xml);
SELECT extractvalue(VALUE(t), '/data/table'),
extractvalue(VALUE(t), '/data/query'),
extractvalue(VALUE(t), '/data/id')
INTO v_queryobj
FROM TABLE(xmlsequence(extract(v_xml, '/data'))) t;
SELECT extractvalue(VALUE(t), '/selection')
BULK COLLECT
INTO v_selection
FROM TABLE(xmlsequence(extract(v_xml, '/data/selection'))) t;
p_id := v_queryobj.id;
--end 解析参数
--获取提交状态，运费，单据编号
SELECT mpp.status，nvl(mpp.amt_tran, 0), mpp.docno, mpp.tot_amtin_pcheck
INTO v_status, v_amt_tran, v_docno, v_tot_amtin_pcheck
FROM m_pur_price mpp
WHERE mpp.id = p_id;
--获取所属采购单ID
SELECT id
INTO v_m_purchase_id
FROM m_purchase
WHERE docno = v_docno;
--如果单据已提交，不允许
IF v_status = 2 THEN
raise_application_error(-20201, '单据已提交，不允许！');
END IF;
/*如果头表的运费不为空，则：更新明细的运费=头表的运费*明细的审核金额/头表的总审核金额（最后多出来的放在最后一行）*/
IF v_amt_tran <> 0 THEN
--获取需求运费和多余运费
OPEN cur_list;
FETCH cur_list BULK COLLECT
INTO v_list;
v_cnt := v_list.count;
IF v_list.count >= 2 THEN
v_mod_amt_tran := v_amt_tran; --起始剩余运费
--设置多行行明细运费
FOR idx IN 1 .. v_list.count - 1 LOOP
SELECT ((v_amt_tran * mppi.tot_amtin_pchecktax) /
v_tot_amtin_pcheck)
INTO v_avg_amt_tran
FROM m_pur_priceitem mppi
WHERE mppi.id = v_list(idx);
UPDATE m_purchaseitem mpi
SET mpi.amt_tran = v_avg_amt_tran
WHERE mpi.id = v_list(idx);
--剩余运费
v_mod_amt_tran := v_mod_amt_tran - v_avg_amt_tran;
END LOOP;
IF v_mod_amt_tran > 0 THEN
--设置最后一行明细运费
UPDATE m_purchaseitem mpi
SET mpi.amt_tran = v_mod_amt_tran
WHERE mpi.id = v_list(v_cnt);
END IF;
ELSIF v_list.count = 1 THEN
--设置单行明细运费
UPDATE m_purchaseitem mpi
SET mpi.amt_tran = v_amt_tran
WHERE mpi.m_purchase_id = v_m_purchase_id;
END IF;
CLOSE cur_list;
END IF;
r_code := 1;
r_message := '平摊运费成功！！';
END;
--新骏：销售核价单-》平摊运费（动作定义）存储过程（author：me）
CREATE OR REPLACE PROCEDURE m_saleamttran_avg(p_user_id IN NUMBER,
p_query IN VARCHAR2,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
---------------------------------------------------------
--author: xuyang
--date: 20180607
/*增加控制：如果单据已提交，不允许。
如果头表的运费不为空，则：
更新明细的运费=头表的运费*明细的审核金额/头表的总审核金额（最后多出来的放在最后一行）
*/
---------------------------------------------------------
--声明用于从p_query解析参数获得单据ID的相关记录和变量
TYPE t_queryobj IS RECORD(
"table" VARCHAR2(255),
query VARCHAR2(32676),
id VARCHAR2(10));
v_queryobj t_queryobj;
TYPE t_selection IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
v_selection t_selection;
st_xml VARCHAR2(32676);
v_xml xmltype;
p_id NUMBER(10); --单据ID
--其他变量定义
v_status m_sale_pck.status%TYPE; --单据状态
v_amt_tran m_sale_pck.amt_tran%TYPE; --单据运费
v_docno m_sale_pck.docno%TYPE; --单据编号
v_tot_amtout_actual m_sale_pck.tot_amtout_actual%TYPE; --总审核金额
v_avg_amt_tran m_sale_pck.amt_tran%TYPE; ----平摊运费
v_mod_amt_tran m_sale_pck.amt_tran%TYPE; --剩余运费
v_m_sale_id m_sale.id%TYPE; --所属销售单ID
/*声明用于获取本单据明细中数量及存储各明细记录ID*/
TYPE type_list IS TABLE OF m_sale.id%TYPE INDEX BY BINARY_INTEGER;
v_list type_list;
v_cnt NUMBER(10); --销售明细中行数
--用于获取销售明细数量的游标
CURSOR cur_list IS
SELECT id
FROM m_sale_pckitem
WHERE m_sale_id = v_m_sale_id;
BEGIN
-- 从p_query解析参数
st_xml := '<data>' || p_query || '</data>';
v_xml := xmltype(st_xml);
SELECT extractvalue(VALUE(t), '/data/table'),
extractvalue(VALUE(t), '/data/query'),
extractvalue(VALUE(t), '/data/id')
INTO v_queryobj
FROM TABLE(xmlsequence(extract(v_xml, '/data'))) t;
SELECT extractvalue(VALUE(t), '/selection')
BULK COLLECT
INTO v_selection
FROM TABLE(xmlsequence(extract(v_xml, '/data/selection'))) t;
p_id := v_queryobj.id;
--end 解析参数
--获取提交状态，运费，单据编号
SELECT msp.status，nvl(msp.amt_tran, 0), msp.docno, msp.tot_amtout_actual
INTO v_status, v_amt_tran, v_docno, v_tot_amtout_actual
FROM m_sale_pck msp
WHERE msp.id = p_id;
--如果单据已提交，不允许
IF v_status = 2 THEN
raise_application_error(-20201, '单据已提交，不允许！');
END IF;
--获取所属销售单ID
SELECT id
INTO v_m_sale_id
FROM m_sale
WHERE docno = v_docno;
/*如果头表的运费不为空，则：更新明细的运费=头表的运费*明细的审核金额/头表的总审核金额（最后多出来的放在最后一行）*/
IF v_amt_tran <> 0 THEN
--获取需求运费和多余运费
OPEN cur_list;
FETCH cur_list BULK COLLECT
INTO v_list;
v_cnt := v_list.count;
IF v_list.count >= 2 THEN
v_mod_amt_tran := v_amt_tran; --起始剩余运费
--设置多行行明细运费
FOR idx IN 1 .. v_list.count - 1 LOOP
SELECT ((v_amt_tran * mspi.tot_amtout_actual) /
v_tot_amtout_actual)
INTO v_avg_amt_tran
FROM m_sale_pckitem mspi
WHERE mspi.id = v_list(idx);
UPDATE m_saleitem msi
SET msi.amt_tran = v_avg_amt_tran
WHERE msi.id = v_list(idx);
--剩余运费
v_mod_amt_tran := v_mod_amt_tran - v_avg_amt_tran;
END LOOP;
IF v_mod_amt_tran > 0 THEN
--设置最后一行明细运费
UPDATE m_saleitem msi
SET msi.amt_tran = v_mod_amt_tran
WHERE msi.id = v_list(v_cnt);
END IF;
ELSIF v_list.count = 1 THEN
--设置单行明细运费
UPDATE m_saleitem msi
SET msi.amt_tran = v_amt_tran
WHERE msi.m_sale_id = v_m_sale_id;
END IF;
CLOSE cur_list;
END IF;
r_code := 1;
r_message := '平摊运费成功！！';
END;
--凰艮：商品销售排行榜报表procedure：
CREATE OR REPLACE PROCEDURE rp_retail_order_generate(p_pi_id NUMBER) IS
/*Version date name reason
0.0 20091222 eiffie created*/
--1.Author:zxx
--Date:20161117
--Modification:取值查询条件的库存店仓，已做单未出库的数量之和（别人发货但还没有出库的数量统计。例如发货店仓是A，做10件销售单已提交未出库，收货店仓B的在单数量为10。单据包含销售单，销售退货单，调拨单）
---------------------------------------------------
--2: author:xuyang
--date:20180608
--modification:
/*
在途库存：取值该店仓、该条码在[库存查询 V_FA_STORAGE]中的在途数量。
预计库存：在途库存+库存数量。
*/
---------------------------------------------------
v_userid NUMBER(10);
v_date VARCHAR2(80);
v_datebegin NUMBER(8);
v_dateend NUMBER(8);
v_sql1 VARCHAR2(4000);
v_sql2 VARCHAR2(4000);
v_cnt NUMBER(10);
r_store r_tabid := r_tabid(); --记录店仓id集合
r_product r_tabid := r_tabid(); --记录款号单id集合
r_qtystore r_tabid := r_tabid(); --记录库存店仓id集合
BEGIN
EXECUTE IMMEDIATE ('truncate TABLE rp_retail_order');
-- raise_application_error(-20201, p_pi_id);
--定义公共sql
v_sql1 := 'SELECT t.info
FROM ad_pinstance_para t
WHERE t.name = :name AND t.ad_pinstance_id = :p_pi_id';
SELECT t.modifierid
INTO v_userid
FROM ad_pinstance t
WHERE t.id = p_pi_id;
--依次获取界面查询条件参数
EXECUTE IMMEDIATE v_sql1
INTO v_date
USING '日期', p_pi_id;
IF v_date IS NULL THEN
--如开始日期和结束日期都没有选择，则查询所有日期范围内数据
v_datebegin := 0;
v_dateend := 20991231;
ELSE
IF instr(v_date, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为20991231
v_dateend := 20991231;
SELECT substr(v_date, 7, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin
FROM dual;
ELSIF instr(v_date, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为0
v_datebegin := 0;
SELECT substr(v_date, 7, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend
FROM dual;
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取
SELECT substr(v_date, 15, 8), substr(v_date, 28, 8) --注意这里是不是分别从17,30开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin, v_dateend
FROM dual;
END IF;
END IF;
EXECUTE IMMEDIATE v_sql1
INTO v_sql2
USING '店仓', p_pi_id;
r_store := f_fast_table(v_sql2);
EXECUTE IMMEDIATE v_sql1
INTO v_sql2
USING '商品', p_pi_id;
r_product := f_fast_table(v_sql2);
EXECUTE IMMEDIATE v_sql1
INTO v_sql2
USING '库存店仓', p_pi_id;
r_qtystore := f_fast_table(v_sql2);
INSERT INTO rp_retail_order
(id, ad_client_id, ad_org_id, m_product_id, qty, tot_amt_actual,
ownerid, modifierid, creationdate, modifieddate, isactive, ad_pi_id)
SELECT get_sequences('RP_RETAIL_ORDER'), a.ad_client_id, a.ad_org_id,
b.m_product_id, SUM(b.qty) qty, SUM(b.tot_amt_actual) amt_actual,
v_userid, v_userid, SYSDATE, SYSDATE, 'Y', p_pi_id
FROM m_retail a, m_retailitem b, TABLE(r_store) rs, TABLE(r_product) rp
WHERE a.id = b.m_retail_id
AND a.c_store_id = rs.id
AND b.m_product_id = rp.id
AND a.status = 2
AND a.billdate BETWEEN v_datebegin AND v_dateend
GROUP BY a.ad_client_id, a.ad_org_id, b.m_product_id;
--begin modification by xuyang 20180608
/*更新以下两字段:
在途库存prein_qty：取值该店仓、该条码在[库存查询 V_FA_STORAGE]中的在途数量。
预计库存pre_qty：在途库存+库存数量。*/
MERGE INTO rp_retail_order g
USING (SELECT c.ad_client_id, c.ad_org_id, c.m_product_id,
SUM(c.qty) qty_storage, SUM(c.qtyprein) qtyprein
FROM fa_storage c, TABLE(r_product) rp, TABLE(r_qtystore) rq
WHERE c.c_store_id = rq.id
AND c.m_product_id = rp.id
GROUP BY c.ad_client_id, c.ad_org_id, c.m_product_id) w
ON (g.m_product_id = w.m_product_id AND g.ad_client_id = w.ad_client_id AND g.ad_org_id = w.ad_org_id AND g.ad_pi_id = p_pi_id)
WHEN MATCHED THEN
UPDATE
SET g.qty_storage = nvl(g.qty_storage, 0) + w.qty_storage,
g.prein_qty = w.qtyprein,
--added by xuyang
g.pre_qty = nvl(g.qty_storage, 0) + w.qty_storage + w.qtyprein --added by xuyang
WHEN NOT MATCHED THEN
INSERT
(id, ad_client_id, ad_org_id, ownerid, modifierid, creationdate,
modifieddate, isactive, ad_pi_id, m_product_id, qty_storage,
prein_qty, pre_qty)
VALUES
(get_sequences('RP_RETAIL_ORDER'), w.ad_client_id, w.ad_org_id,
v_userid, v_userid, SYSDATE, SYSDATE, 'Y', p_pi_id, w.m_product_id,
w.qty_storage, w.qtyprein, (w.qtyprein + w.qty_storage)); --added by xuyang
--end modification by xuyang 20180608
--add by zxx 20161117 在单数量2
MERGE INTO rp_retail_order a
USING (SELECT nvl(SUM(g.qtypreout), 0) AS qtypreout, /* g.c_store_id,*/
g.m_product_id
FROM (SELECT nvl(SUM(mi.qty), 0) AS qtypreout,
/* m.c_dest_id AS c_store_id, */ mi.m_product_id
FROM m_sale m, TABLE(r_qtystore) rs, TABLE(r_product) rp,
m_saleitem mi
WHERE m.c_dest_id = rs.id
AND mi.m_sale_id = m.id
AND mi.m_product_id = rp.id
AND m.status = 2
AND m.out_status = 1
GROUP BY /*m.c_dest_id,*/ mi.m_product_id
UNION ALL
SELECT nvl(SUM(mri.qty), 0) AS qtypreout,
/* mr.c_store_id AS c_store_id,*/ mri.m_product_id
FROM m_ret_sale mr, TABLE(r_qtystore) rs, TABLE(r_product) rp,
m_ret_saleitem mri
WHERE mr.c_store_id = rs.id
AND mri.m_ret_sale_id = mr.id
AND mri.m_product_id = rp.id
AND mr.status = 2
AND mr.out_status = 1
GROUP BY /* mr.c_store_id,*/ mri.m_product_id
UNION ALL
SELECT nvl(SUM(mti.qty), 0) AS qtypreout,
/* mt.c_dest_id AS c_store_id,*/ mti.m_product_id
FROM m_transfer mt, TABLE(r_qtystore) rs, TABLE(r_product) rp,
m_transferitem mti
WHERE mt.c_dest_id = rs.id
AND mti.m_product_id = rp.id
AND mti.m_transfer_id = mt.id
AND mt.status = 2
AND mt.out_status = 1
GROUP BY /*mt.c_dest_id,*/ mti.m_product_id) g
GROUP BY /* g.c_store_id,*/ g.m_product_id) tt
ON ( /*a.c_store_id = tt.c_store_id AND */
a.m_product_id = tt.m_product_id)
WHEN MATCHED THEN
UPDATE
SET a.qtypreout2 = tt.qtypreout;
--end by zxx 20161117
UPDATE rp_retail_order g
SET pricelist = (SELECT pricelist
FROM m_product a
WHERE a.id = g.m_product_id)
WHERE g.ad_pi_id = p_pi_id;
UPDATE rp_retail_order g
SET rateamt = (SELECT decode(SUM(a.tot_amt_actual), 0, 0,
g.tot_amt_actual / SUM(a.tot_amt_actual))
FROM rp_retail_order a
WHERE a.ad_pi_id = p_pi_id);
SELECT length(COUNT(1))
INTO v_cnt
FROM rp_retail_order t
WHERE t.ad_pi_id = p_pi_id;
UPDATE rp_retail_order g
SET n = (SELECT substr('00000000000' || n, -v_cnt, v_cnt)
FROM (SELECT id,
rank() over(PARTITION BY ad_client_id, ad_org_id ORDER BY nvl(qty, 0) DESC) n
FROM rp_retail_order a
WHERE a.ad_pi_id = p_pi_id) w
WHERE w.id = g.id)
WHERE EXISTS (SELECT 1
FROM rp_retail_order a
WHERE g.id = a.id)
AND g.ad_pi_id = p_pi_id;
END; /* edit by shizhishu 2016/11/18 16:39:15 */
--MY FIRST REPORT TABLE MODEL
CREATE OR REPLACE PROCEDURE rp_o2o_salesources_gen(p_pi_id NUMBER) IS
---------------------------------------------------
--Author:xuyang
-- Date:20180615
-- Author :xuyang
-- Description :
/*
查询条件：单据日期（日期范围、必填） 、订单来源（下拉框选项：EBSOURCE，非必填）
查询逻辑：
取查询单据日期在查询条件的开始日期和结束日期内、
订单来源为查询条件的订单来源、 已提交、未结案、可用的 【云仓订单】的数据。
(--PS: 控制查询条件的结束日期-开始日期=6，如果不等于6，则报错‘该报表为周报表，请以7天为一个周期’)
查询结果：
行定义：
序号：显示1,2,3,4……。
渠道：取值符合条件的云仓订单主表的【接口订单类型】。
订单来源：取值符合条件的云仓订单主表的【订单来源】。
汇总字段：
销量：取值符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单数量】的汇总。
销售额(万元)：取值符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单成交金额】/10000，四舍五入取整。
销量占比(%)：取值(本条记录的销量/所有订单来源销量之和 )*100，百分比格式，百分比之后显示两位小数。
销售额占比(%)：取值（本条记录的销售额(万元) / 所有订单来源销售额之和）*100，百分比格式，百分比之后显示两位小数。
销量周环比(%)：取值[(本条记录的销量-上周销量)/上周销量]*100，本周为查询日期选择的起始日期和结束日期，上周为本周的上一个7天作为一周；百分比格式，百分比之后显示两位小数。
销售额周环比(%)：取值[(本条记录的销售额(万元)-上周销售额（万元）)/上周销售额（万元）]*100，本周为查询日期选择的起始日期和结束日期，上周为本周的上一个7天作为一周；百分比格式，百分比之后显示两位小数。
件单价(元) ：销售额(万元)*10000/ 销量，四舍五入取整。
成交折扣：取值本记录的销售额(万元)*10000/ 符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单标准金额】，小数点后面两位。
--PS:销量占比(%)、销售额占比(%)、销量周环比(%)、销售额周环比(%)、件单价、成交折扣合计时，取平均数。
*/
---------------------------------------------------
v_userid NUMBER(10);
v_date VARCHAR2(80);
v_datebegin VARCHAR2(8);
v_dateend VARCHAR2(8);
v_sql1 VARCHAR2(4000);
--v_sql2 VARCHAR2(4000);
v_source rp_o2o_salesources.source%TYPE; --订单来源
v_tmp_source rp_o2o_salesources.source%TYPE;
v_loc1 NUMBER(10); --订单来源字符解析位置1
v_loc2 NUMBER(10); --订单来源字符解析位置2
v_days NUMBER(10);
v_all_tot_num rp_o2o_salesources.num%TYPE; --所有订单来源销量之和
v_all_tot_amt rp_o2o_salesources.tot_amt_actual%TYPE; --所有订单来源销售额之和
--v_sales_last rp_o2o_salesources.num%TYPE; --上周销量
--v_sales_amt_last rp_o2o_salesources.tot_amt_actual%TYPE; --上周销售额
BEGIN
EXECUTE IMMEDIATE ('truncate TABLE RP_O2O_SALESOURCES');
-- raise_application_error(-20201, p_pi_id);
--定义公共sql
v_sql1 := 'SELECT t.info
FROM ad_pinstance_para t
WHERE t.name = :name AND t.ad_pinstance_id = :p_pi_id';
SELECT t.modifierid
INTO v_userid
FROM ad_pinstance t
WHERE t.id = p_pi_id;
--依次获取界面查询条件参数
EXECUTE IMMEDIATE v_sql1
INTO v_date
USING 'BILLDATE', p_pi_id;
--raise_application_error(-20201, 'debug:' || v_date);
-- debug: (BILLDATE>=20180605)
IF v_date IS NULL THEN
--如开始日期和结束日期都没有选择，
raise_application_error(-20201, '请选择日期范围！');
ELSE
IF instr(v_date, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为开始日后六天
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin
FROM dual;
--raise_application_error(-20201, 'debug: ' || v_datebegin);
--结束日期默认为开始日后六天
v_dateend := to_char(to_date(v_datebegin, 'yyyymmdd') + 6,
'yyyymmdd');
ELSIF instr(v_date, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为前六天BILLDATE<=20180615
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend
FROM dual;
--开始日期默认为前六天
v_datebegin := to_char(to_date(v_dateend, 'yyyymmdd') - 6,
'yyyymmdd');
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取debug: ( BILLDATE BETWEEN 20180605 AND 20180615)
--raise_application_error(-20201, 'debug:' || v_date);
SELECT substr(v_date, 21, 8), substr(v_date, 34, 8) --注意这里是不是分别从20,33开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin, v_dateend
FROM dual;
/*raise_application_error(-20201,
'debug:' || v_datebegin || '--' ||
v_dateend);*/
SELECT to_date(v_dateend, 'yyyymmdd') -
to_date(v_datebegin, 'yyyymmdd')
INTO v_days
FROM dual;
--raise_application_error(-20201, 'debug:' || v_days);
IF v_days <> 6 THEN
raise_application_error(-20201, '该报表为周报表，请以7天为一个周期！');
END IF;
END IF;
END IF;
--获取订单来源
EXECUTE IMMEDIATE v_sql1
INTO v_tmp_source
USING 'SOURCE', p_pi_id;
--raise_application_error(-20201, 'debug:' || v_days);
--debug: (SOURCE LIKE '%0%')
--debug: (SOURCE = 'taobao')
--debug: (SOURCE = 'HAND')
--debug: (SOURCE = 'WEB')
--未选择订单来源，则给予提示
IF v_tmp_source IS NULL THEN
raise_application_error(-20201, '请选择订单来源！');
END IF;
--订单来源为查询条件的订单来源、 已提交、未结案、可用的 【云仓订单】的数据。
/*
销量：取值符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单数量】的汇总。
销售额(万元)：取值符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单成交金额】/10000，四舍五入取整。
销量占比(%)：取值(本条记录的销量/所有订单来源销量之和 )*100，百分比格式，百分比之后显示两位小数。
销售额占比(%)：取值（本条记录的销售额(万元) / 所有订单来源销售额之和）*100，百分比格式，百分比之后显示两位小数。
件单价(元) ：销售额(万元)*10000/ 销量，四舍五入取整。
成交折扣：取值本记录的销售额(万元)*10000/ 符合查询条件的、该渠道、该订单来源对应的云仓订单主表【订单标准金额】，小数点后面两位。
--PS:销量占比(%)、销售额占比(%)、销量周环比(%)、销售额周环比(%)、件单价、成交折扣合计时，取平均数。
*/
--获取所有订单销量和销售额之和
SELECT nvl(SUM(os.tot_amt_actual), 0), nvl(SUM(os.num), 0)
INTO v_all_tot_amt, v_all_tot_num
FROM o2o_so os;
IF instr(v_tmp_source, '=') > 0 THEN
--获取字符‘位置
v_loc1 := instr(v_tmp_source, '''');
v_loc2 := instr(v_tmp_source, '''', v_loc1 + 1);
--raise_application_error(-20201, 'debug:' || v_loc1 || '---' || v_loc2);
--获取到订单来源
v_source := substr(v_tmp_source, v_loc1 + 1, v_loc2 - v_loc1 - 1);
--临时表插入数据
INSERT INTO rp_o2o_salesources
(id, ad_client_id, ad_org_id, interface_type, SOURCE, num,
tot_amt_actual, price, deal_discount, ownerid, modifierid,
creationdate, modifieddate, isactive, ad_pi_id, no, sales_rate,
sales_amt_rate)
SELECT get_sequences('rp_o2o_salesources'), a.ad_client_id,
a.ad_org_id, a.interface_type, a.source, SUM(a.num),
round(SUM(a.tot_amt_actual) / 10000),
round(((round(SUM(a.tot_amt_actual) / 10000)) * 10000) /
SUM(a.num)),
trunc((((round(SUM(a.tot_amt_actual) / 10000)) * 10000) /
SUM(a.tot_amt_list)), 2), v_userid, v_userid, SYSDATE,
SYSDATE, 'Y', p_pi_id,
dense_rank() over(PARTITION BY NULL ORDER BY SUM(a.tot_amt_actual)),
trunc((SUM(a.num) / v_all_tot_num) * 100, 2),
trunc((SUM(tot_amt_actual) / v_all_tot_amt) * 100, 2)
FROM o2o_so a
WHERE a.status = 2
AND a.isactive = 'Y'
AND a.close_status = 1
AND a.source = v_source
AND to_date(a.billdate, 'yyyymmdd') BETWEEN
to_date(v_datebegin, 'yyyymmdd') AND
to_date(v_dateend, 'yyyymmdd')
GROUP BY a.ad_client_id, a.ad_org_id, a.interface_type, a.source
ORDER BY SUM(a.num);
ELSIF instr(v_tmp_source, 'LIKE') > 0 THEN
INSERT INTO rp_o2o_salesources
(id, ad_client_id, ad_org_id, interface_type, SOURCE, num,
tot_amt_actual, price, deal_discount, ownerid, modifierid,
creationdate, modifieddate, isactive, ad_pi_id, no, sales_rate,
sales_amt_rate)
SELECT get_sequences('rp_o2o_salesources'), a.ad_client_id,
a.ad_org_id, a.interface_type, a.source, SUM(a.num),
round(SUM(a.tot_amt_actual) / 10000),
round(((round(SUM(a.tot_amt_actual) / 10000)) * 10000) /
SUM(a.num)),
trunc((((round(SUM(a.tot_amt_actual) / 10000)) * 10000) /
SUM(a.tot_amt_list)), 2), v_userid, v_userid, SYSDATE,
SYSDATE, 'Y', p_pi_id,
dense_rank() over(PARTITION BY NULL ORDER BY SUM(a.tot_amt_actual)),
trunc((SUM(a.num) / v_all_tot_num) * 100, 2),
trunc((SUM(tot_amt_actual) / v_all_tot_amt) * 100, 2)
FROM o2o_so a
WHERE a.status = 2
AND a.isactive = 'Y'
AND a.close_status = 1
AND to_date(a.billdate, 'yyyymmdd') BETWEEN
to_date(v_datebegin, 'yyyymmdd') AND
to_date(v_dateend, 'yyyymmdd')
GROUP BY a.ad_client_id, a.ad_org_id, a.interface_type, a.source
ORDER BY SUM(a.num);
END IF;
/*
销量周环比(%)：取值[(本条记录的销量-上周销量)/上周销量]*100，本周为查询日期选择的起始日期和结束日期，上周为本周的上一个7天作为一周；百分比格式，百分比之后显示两位小数。
销售额周环比(%)：取值[(本条记录的销售额(万元)-上周销售额（万元）)/上周销售额（万元）]*100，本周为查询日期选择的起始日期和结束日期，上周为本周的上一个7天作为一周；百分比格式，百分比之后显示两位小数。
*/
MERGE INTO rp_o2o_salesources rps
USING (SELECT os.interface_type interface_type, os.source SOURCE,
SUM(os.num) tot_num_last, SUM(os.tot_amt_actual) tot_amt_last
FROM o2o_so os
WHERE to_date(os.billdate, 'yyyymmdd') BETWEEN
to_date(v_datebegin, 'yyyymmdd') - 6 AND
to_date(v_dateend, 'yyyymmdd') - 6
GROUP BY os.interface_type, os.source) rs
ON (rps.interface_type = rs.interface_type AND rps.source = rs.source)
WHEN MATCHED THEN
UPDATE
SET rps.sales_week_rate = trunc(((rps.num - rs.tot_num_last) /
rs.tot_num_last) * 100, 2),
rps.sales_amtweek_rate = trunc(((rps.tot_amt_actual -
rs.tot_amt_last) / rs.tot_amt_last) * 100,
2);
END;
---V1.0-20180608-06全渠道各渠道TOP10销售分析报表存储过程
CREATE OR REPLACE PROCEDURE rp_o2o_salesources10_gen(p_pi_id NUMBER) IS
---------------------------------------------------
--Author:xuyang
-- Date:20180616
-- Author :xuyang
-- Description :
/*
查询条件：单据日期（日期范围、必填） 、订单来源（下拉框选项：EBSOURCE，非必填）
查询逻辑：
查询逻辑：取查询单据日期在查询条件的开始日期和结束日期内、订单来源为查询条件的订单来源、 已提交、未结案、可用的 【云仓订单】的数据
(--PS: 控制查询条件的结束日期-开始日期=6，如果不等于6，则报错‘该报表为周报表，请以7天为一个周期’)
查询结果：
行定义：
序号：显示1,2,3,4……按照本订单来源的销量降序，取前十名。--即：每个订单来源只统计【销量】排行前十的款号
订单来源：取值符合条件的云仓订单主表的【订单来源】。
款号：取值符合条件的云仓订单明细的【款号】。
汇总字段：
销量：取值符合查询条件的、该订单来源、该款号对应的云仓订单明细的【订单数量】的汇总。
销售额(元)：取值符合查询条件的、该订单来源、该款号对应的云仓订单明细的【订单成交金额】的汇总，四舍五入取整。
销量占比(%)：取值(本条记录的销量/所有订单来源销量之和 )*100，百分比格式，百分比之后显示两位小数。
销售额占比(%)：取值（本条记录的销售额(元) / 所有订单来源销售额之和）*100，百分比格式，百分比之后显示两位小数。
*/
---------------------------------------------------
v_userid NUMBER(10);
v_date VARCHAR2(80);
v_datebegin VARCHAR2(8);
v_dateend VARCHAR2(8);
v_sql1 VARCHAR2(4000);
v_source rp_o2o_salesources10.source%TYPE; --订单来源
v_tmp_source rp_o2o_salesources10.source%TYPE;
v_loc1 NUMBER(10); --订单来源字符解析位置1
v_loc2 NUMBER(10); --订单来源字符解析位置2
v_all_tot_num rp_o2o_salesources10.num%TYPE; --所有订单来源销量之和
v_all_tot_amt rp_o2o_salesources10.tot_amt_actual%TYPE; --所有订单来源销售额之和
BEGIN
EXECUTE IMMEDIATE ('truncate TABLE RP_O2O_SALESOURCES');
-- raise_application_error(-20201, p_pi_id);
--定义公共sql
v_sql1 := 'SELECT t.info
FROM ad_pinstance_para t
WHERE t.name = :name AND t.ad_pinstance_id = :p_pi_id';
SELECT t.modifierid
INTO v_userid
FROM ad_pinstance t
WHERE t.id = p_pi_id;
--依次获取界面查询条件参数
EXECUTE IMMEDIATE v_sql1
INTO v_date
USING 'BILLDATE', p_pi_id;
--raise_application_error(-20201, 'debug:' || v_date);
-- debug: (BILLDATE>=20180605)
IF v_date IS NULL THEN
--如开始日期和结束日期都没有选择，
raise_application_error(-20201, '请选择日期范围！');
ELSE
IF instr(v_date, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为开始日后六天
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_datebegin
FROM dual;
--raise_application_error(-20201, 'debug: ' || v_datebegin);
--结束日期默认为开始日后六天
v_dateend := to_char(to_date(v_datebegin, 'yyyymmdd') + 6,
'yyyymmdd');
ELSIF instr(v_date, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认为前六天BILLDATE<=20180615
SELECT substr(v_date, 13, 8) --注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
INTO v_dateend
FROM dual;
--开始日期默认为前六天
v_datebegin := to_char(to_date(v_dateend, 'yyyymmdd') - 6,
'yyyymmdd');
ELSE
--如开始日期和结束日期都进行了选择，则均从参数值获取debug: ( BILLDATE BETWEEN 20180605 AND 20180615)
--raise_application_error(-20201, 'debug:' || v_date);
SELECT substr(v_date, 21, 8), substr(v_date, 34, 8) --注意这里是不是分别从20,33开始，也是取决于日期参数定义，，例如日期参数名定义为DATE，则此处为17、30，如定义为BILLDATE，此处为21、34，以此类推
INTO v_datebegin, v_dateend
FROM dual;
END IF;
END IF;
--获取订单来源
EXECUTE IMMEDIATE v_sql1
INTO v_tmp_source
USING 'SOURCE', p_pi_id;
--raise_application_error(-20201, 'debug:' || v_days);
--未选择订单来源，则给予提示
IF v_tmp_source IS NULL THEN
raise_application_error(-20201, '请选择订单来源！');
END IF;
--订单来源为查询条件的订单来源、 已提交、未结案、可用的 【云仓订单】的数据。
/*
销量：取值符合查询条件的、该订单来源、该款号对应的云仓订单明细的【订单数量】的汇总。
销售额(元)：取值符合查询条件的、该订单来源、该款号对应的云仓订单明细的【订单成交金额】的汇总，四舍五入取整。
销量占比(%)：取值(本条记录的销量/所有订单来源销量之和 )*100，百分比格式，百分比之后显示两位小数。
销售额占比(%)：取值（本条记录的销售额(元) / 所有订单来源销售额之和）*100，百分比格式，百分比之后显示两位小数。
*/
--获取所有订单销量和销售额之和
SELECT nvl(SUM(os.tot_amt_actual), 0), nvl(SUM(os.num), 0)
INTO v_all_tot_amt, v_all_tot_num
FROM o2o_so os;
IF instr(v_tmp_source, '=') > 0 THEN
--获取字符‘位置
v_loc1 := instr(v_tmp_source, '''');
v_loc2 := instr(v_tmp_source, '''', v_loc1 + 1);
--获取到订单来源
v_source := substr(v_tmp_source, v_loc1 + 1, v_loc2 - v_loc1 - 1);
--临时表插入数据，插入指定订单来源的数据
INSERT INTO rp_o2o_salesources10
(id, ad_client_id, ad_org_id, SOURCE, num, tot_amt_actual, ownerid,
modifierid, creationdate, modifieddate, isactive, ad_pi_id,
sales_rate, sales_amt_rate, m_product_id, no)
SELECT ab.id, ab.ad_clien_id, ab.ad_org_id, ab.source, ab.tot_qty,
ab.tot_amt, v_userid, v_userid, SYSDATE, SYSDATE, 'Y',
p_pi_id, ab.sales_rate, ab.sales_amt_rate, ab.m_product_id,
dense_rank() over(PARTITION BY NULL ORDER BY ab.tot_qty DESC)
FROM (SELECT get_sequences('rp_o2o_salesources') id,
a.ad_client_id ad_clien_id, a.ad_org_id ad_org_id,
a.source SOURCE, SUM(osi.qty) tot_qty,
round(SUM(osi.tot_amt_actual)) tot_amt,
dense_rank() over(PARTITION BY a.source ORDER BY SUM(osi.qty) DESC) ct,
trunc((SUM(osi.qty) / v_all_tot_num) * 100, 2) sales_rate,
trunc((SUM(osi.tot_amt_actual) / v_all_tot_amt) * 100, 2) sales_amt_rate,
osi.m_product_id m_product_id
FROM o2o_so a
JOIN o2o_soitem osi
ON (osi.eb_orderso_id = a.id)
WHERE a.status = 2
AND a.isactive = 'Y'
AND a.close_status = 1
AND a.source = v_source
AND to_date(a.billdate, 'yyyymmdd') BETWEEN
to_date(v_datebegin, 'yyyymmdd') AND
to_date(v_dateend, 'yyyymmdd')
GROUP BY a.ad_client_id, a.ad_org_id, a.source,
osi.m_product_id
ORDER BY SUM(osi.qty) DESC) ab
WHERE ab.ct <= 10;
ELSIF instr(v_tmp_source, 'LIKE') > 0 THEN
--插入所有符合条件的订单来源数据
INSERT INTO rp_o2o_salesources10
(id, ad_client_id, ad_org_id, SOURCE, num, tot_amt_actual, ownerid,
modifierid, creationdate, modifieddate, isactive, ad_pi_id,
sales_rate, sales_amt_rate, m_product_id, no)
SELECT ab.id, ab.ad_clien_id, ab.ad_org_id, ab.source, ab.tot_qty,
ab.tot_amt, v_userid, v_userid, SYSDATE, SYSDATE, 'Y',
p_pi_id, ab.sales_rate, ab.sales_amt_rate, ab.m_product_id,
dense_rank() over(ORDER BY ab.tot_qty DESC)
FROM (SELECT get_sequences('rp_o2o_salesources') id,
a.ad_client_id ad_clien_id, a.ad_org_id ad_org_id,
a.source SOURCE, SUM(osi.qty) tot_qty,
round(SUM(osi.tot_amt_actual)) tot_amt,
dense_rank() over(PARTITION BY a.source ORDER BY SUM(osi.qty) DESC) ct,
trunc((SUM(osi.qty) / v_all_tot_num) * 100, 2) sales_rate,
trunc((SUM(osi.tot_amt_actual) / v_all_tot_amt) * 100, 2) sales_amt_rate,
osi.m_product_id m_product_id
FROM o2o_so a
JOIN o2o_soitem osi
ON (osi.eb_orderso_id = a.id)
WHERE a.status = 2
AND a.isactive = 'Y'
AND a.close_status = 1
AND to_date(a.billdate, 'yyyymmdd') BETWEEN
to_date(v_datebegin, 'yyyymmdd') AND
to_date(v_dateend, 'yyyymmdd')
GROUP BY a.ad_client_id, a.ad_org_id, a.source,
osi.m_product_id
ORDER BY SUM(osi.qty) DESC) ab
WHERE ab.ct <= 10;
END IF;
END;
--------------------------------------------
--解析Json数据
CREATE OR REPLACE PROCEDURE test_proc(p1 VARCHAR2) AS v_json_varchar2 VARCHAR2(4000);
injson json;
paramlist json_list;
onejson json;
--第一层
v_marketcode VARCHAR2(8);
v_marketname VARCHAR2(64);
v_address VARCHAR2(64);
v_tel VARCHAR2(11);
--第二层
v_name VARCHAR2(64);
v_fruitcode VARCHAR2(8);
BEGIN
--定义json数据，当然json数据也可从参数中传来
v_json_varchar2 := '{
"marketcode": "123456",
"marketname": "好吃的水果店",
"address": "一个好地方",
"tel": "12345678901",
"fruitlist": {
"name": "apple",
"fruitcode": "223344",
"applelist": [
{
"applename": "redapple ",
"applecode": "111000",
"price": "10"
},
{
"applename": "greenapple ",
"applecode": "111111",
"price": "12"
},
{
"applename": "yellowapple ",
"applecode": "111222",
"price": "8"
}
]
}
}';
injson := json(v_json_varchar2);
--获取第一层json值
v_marketcode := json_ext.get_string(injson, 'marketcode');
v_marketname := json_ext.get_string(injson, 'marketname');
v_address := json_ext.get_string(injson, 'address');
v_tel := json_ext.get_string(injson, 'tel');
--第二层
v_name := json_ext.get_string(injson, 'fruitlist.name');
v_fruitcode := json_ext.get_string(injson, 'fruitlist.fruitcode');
--接下来获取第三层，使用json_list来存放json列表
paramlist := json_list();
onejson := json();
paramlist := json_ext.get_json_list(injson, 'fruitlist.applelist');
--使用循环返回每个json部分的值
FOR i IN 1 .. paramlist.count LOOP
--读取每个品种具体信息
onejson := json(paramlist.get_elem(i));
dbms_output.put_line(json_ext.get_string(onejson, 'applename'));
dbms_output.put_line(json_ext.get_string(onejson, 'applecode'));
dbms_output.put_line(json_ext.get_string(onejson, 'price'));
END LOOP;
END;
-----------------------------------------------------------------------------------
注：将clob类型字段读取出来到变量，需dbms_lob.substr(xms.param)转化为varchar2类型
CREATE OR REPLACE PROCEDURE xq_midso_gen AS
----------------------------------------------------------
--author:xuyang
--date:20180627
--description:
/*
FOR (查询实际表[订单接口 XQ_MIDSO]中的[是否生成订单]为Y的记录) LOOP
根据接口信息的json信息生成未提交的发货订单。
生成发货订单头表：
单据日期取SYSDATE。
订单类型取新货订单。
发货店仓取总部店仓。
收货店仓取接口中的customer_name对应的经销商下的任一店仓（根据名称匹配）。
鲜桥订单编码取接口中的ordercode。
备注：由鲜桥接口自动生成！
调用存储过程：B_SO_AC。
生成发货订单明细：
条码、款号、ASI取接口中的product_name对应条码的值。
数量取接口中的amount。
调用存储过程：B_SOITEM_ACM。
调用存储过程：B_SO_AM。
END LOOP;
*/
/*
*/
----------------------------------------------------------
v_injson json; --用于将接口信息转换为json格式
--v_ordercode b_so.xq_ordercode%TYPE; --发货订单鲜桥订单编码
--v_customer_name c_customer.name%TYPE; --订单头信息中的经销商名称
v_delivery_time VARCHAR2(20); --配送时间(发货日期)
--v_delivery_address b_so.dest_address%TYPE; --收货地址
v_clob_varchar2 VARCHAR2(4000);
BEGIN
--查询实际表[订单接口 XQ_MIDSO]中的[是否生成订单]为Y的记录
FOR v_list IN (SELECT xms.id, xms.ad_client_id, xms.ad_org_id,
xms.creationdate, xms.ownerid, xms.errormeg
FROM xq_midso xms
WHERE xms.is_so = 'Y') LOOP
SELECT dbms_lob.substr(xms.param)
INTO v_clob_varchar2
FROM xq_midso xms
WHERE xms.id = v_list.id;
v_injson := json(v_clob_varchar2);
--获取headerlist:orderHeader
--v_id := json_ext.get_string(v_injson, 'CallInfo.orderHeader.id');
--v_ordercode := json_ext.get_string(v_injson,
--'CallInfo.orderHeader.ordercode');
--v_customer_name := json_ext.get_string(v_injson,'CallInfo.orderHeader.customer_name');
v_delivery_time := substr(json_ext.get_string(v_injson,
'CallInfo.orderHeader.delivery_time'),
1, 10);
dbms_output.put_line('v_delivery_time:' ||
to_number(REPLACE(v_delivery_time, '-', '')));
END LOOP;
END;
------------------------------------------------------------
*************解析json格式数据实例*******************
--新骏：订单接口，生成发货订单（自动任务：每5分钟运行一次）mantis:0029682
CREATE OR REPLACE PROCEDURE xq_midso_gen(p_id IN NUMBER) AS
----------------------------------------------------------
--author:xuyang
--date:20180627
--description:
/*
FOR (查询实际表[订单接口 XQ_MIDSO]中的[是否生成订单]为Y的记录) LOOP
根据接口信息的json信息生成未提交的发货订单。
生成发货订单头表：
单据日期取SYSDATE。
订单类型取新货订单。
发货店仓取总部店仓。
收货店仓取接口中的customer_name对应的经销商下的任一店仓（根据名称匹配）。
鲜桥订单编码取接口中的ordercode。
备注：由鲜桥接口自动生成！
调用存储过程：B_SO_AC。
生成发货订单明细：
条码、款号、ASI取接口中的product_name对应条码的值。
数量取接口中的amount。
调用存储过程：B_SOITEM_ACM。
调用存储过程：B_SO_AM。
END LOOP;
*/
/*
*/
----------------------------------------------------------
v_injson json; --用于将接口信息转换为json格式
v_b_so_id b_so.id%TYPE; --要使用的发货订单ID
v_b_so_docno b_so.docno%TYPE; --发货订单据编号
v_table_id ad_table.id%TYPE; --发货订单m_agtpur表的id
v_ordercode b_so.xq_ordercode%TYPE; --发货订单鲜桥订单编码
v_c_store_id c_store.id%TYPE; --总部店仓ID
v_customer_name c_customer.name%TYPE; --订单头信息中的经销商名称
v_cc_store_id c_store.id%TYPE; --经销商下任一id
v_delivery_time VARCHAR2(20); --配送时间(发货日期)
v_delivery_time1 NUMBER(8); --配送时间(发货日期)
v_delivery_address b_so.dest_address%TYPE; --收货地址
v_amount b_soitem.qty%TYPE; --数量
v_bodylist json_list; --订单明细信息列表
v_onejson json; --用于解析每一个明细数据
v_m_product_id m_product.id%TYPE; --款号
v_m_productalias_name m_product_alias.no%TYPE; --条码名
v_m_productalias_id m_product_alias.id%TYPE; --条码id
v_asi b_soitem.m_attributesetinstance_id%TYPE; --asi
v_b_soitem_id b_soitem.id%TYPE; --发货订单明细id
v_code NUMBER(3);
v_message VARCHAR2(500);
v_clob_varchar2 VARCHAR2(4000);
BEGIN
--查询实际表[订单接口 XQ_MIDSO]中的[是否生成订单]为Y的记录
FOR v_list IN (SELECT xms.id, xms.ad_client_id, xms.ad_org_id,
xms.creationdate, xms.ownerid, xms.errormeg
FROM xq_midso xms
WHERE xms.is_so = 'Y') LOOP
BEGIN
--获取接口信息
SELECT dbms_lob.substr(xms.param)
INTO v_clob_varchar2
FROM xq_midso xms
WHERE xms.id = v_list.id;
v_injson := json(v_clob_varchar2);
--获取headerlist:orderHeader
--v_id := json_ext.get_string(v_injson, 'CallInfo.orderHeader.id');
v_ordercode := json_ext.get_string(v_injson,
'CallInfo.orderHeader.ordercode');
v_customer_name := json_ext.get_string(v_injson,
'CallInfo.orderHeader.customer_name');
v_delivery_time := substr(json_ext.get_string(v_injson,
'CallInfo.orderHeader.delivery_time'),
1, 10);
v_delivery_time1 := to_number(REPLACE(v_delivery_time, '-', ''));
v_delivery_address := json_ext.get_string(v_injson,
'CallInfo.orderHeader.delivery_address');
--获取表b_so的id
SELECT id
INTO v_table_id
FROM ad_table
WHERE NAME = upper('b_so');
--自动生成单据编号
SELECT t.sequencename
INTO v_b_so_docno
FROM ad_column t
WHERE t.ad_table_id = v_table_id
AND t.dbname = 'DOCNO';
v_b_so_docno := get_sequenceno(v_b_so_docno, v_list.ad_client_id);
--获取要使用的发货订单id
v_b_so_id := get_sequences('B_SO');
--获取总部店仓ID
SELECT cs.id
INTO v_c_store_id
FROM c_store cs
WHERE cs.name = '总部仓库'
AND cs.ad_client_id = v_list.ad_client_id;
BEGIN
--获取对应经销商下任一店仓ID：v_cc_store_id
SELECT nvl(cs.id, 0)
INTO v_cc_store_id
FROM c_store cs
JOIN c_customer ccu
ON (cs.c_customer_id = ccu.id AND ccu.name = v_customer_name)
WHERE rownum <= 1;
EXCEPTION
WHEN no_data_found THEN
v_cc_store_id := NULL;
END;
/*
生成发货订单头表：
单据日期取SYSDATE。
订单类型取新货订单。
发货店仓取总部店仓。
收货店仓取接口中的customer_name对应的经销商下的任一店仓（根据名称匹配）。
鲜桥订单编码取接口中的ordercode。
备注：由鲜桥接口自动生成！
调用存储过程：B_SO_AC。
*/
--dbms_output.put_line('xuyang123:v_b_so_docno:' || v_b_so_docno);
BEGIN
INSERT INTO b_so
(id, billdate, docno, doctype, c_store_id, c_dest_id,
xq_ordercode, predateout, dest_address, description,
ad_client_id, ad_org_id, ownerid, creationdate, status,
isactive)
VALUES
(v_b_so_id, to_number(to_char(SYSDATE, 'yyyymmdd')),
v_b_so_docno, 'FWD', v_c_store_id, v_cc_store_id,
v_ordercode, v_delivery_time1, v_delivery_address,
'由鲜桥接口自动生成！', v_list.ad_client_id, v_list.ad_org_id,
v_list.ownerid, SYSDATE, 1, 'Y');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('(debug)订单接口记录id：' || v_list.id ||
',生成发货订单发生异常:' || SQLERRM);
CONTINUE;
END;
--调用存储过程：B_SO_AC。
BEGIN
b_so_ac(v_b_so_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('发货订单记录id：' || v_b_so_id ||
',调用发货订单ac程序b_so_ac发生异常:' || SQLERRM);
END;
/*获取明细数据，插入到发货订单明细表*/
/*
生成发货订单明细：
条码、款号、ASI取接口中的product_name对应条码的值。
数量取接口中的amount。
调用存储过程：B_SOITEM_ACM。
调用存储过程：B_SO_AM。
*/
v_bodylist := json_list();
v_onejson := json();
--获取body:orderBody
v_bodylist := json_ext.get_json_list(v_injson, 'CallInfo.orderBody');
--循环获取明细数据，并插入数据库中
FOR idx IN 1 .. v_bodylist.count LOOP
--读取每个明细信息
v_onejson := json(v_bodylist.get_elem(idx));
v_m_productalias_name := json_ext.get_string(v_onejson,
'product_name');
v_amount := to_number(json_ext.get_string(v_onejson, 'amount'));
--获取品名对应的款号，条码，asi
BEGIN
SELECT mpa.id, mpa.m_product_id,
mpa.m_attributesetinstance_id
INTO v_m_productalias_id, v_m_product_id, v_asi
FROM m_product_alias mpa
WHERE mpa.no = v_m_productalias_name;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('条码：' || v_m_productalias_name ||
',在条码档案中不存在！');
CONTINUE;
END;
--获取即将使用的发货订单明细记录id
v_b_soitem_id := get_sequences('bo_soitem');
--插入发货订单明细表
INSERT INTO b_soitem
(id, b_so_id, m_productalias_id, m_product_id,
m_attributesetinstance_id, qty)
VALUES
(v_b_soitem_id, v_b_so_id, v_m_productalias_id,
v_m_product_id, v_asi, v_amount);
--调用存储过程：B_SOITEM_ACM
BEGIN
b_soitem_acm(v_b_soitem_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('发货订单明细记录id：' || v_b_soitem_id ||
',调用发货订单明细acm程序b_soitem_acm发生异常:' ||
SQLERRM);
END;
--调用存储过程：B_SO_AM
BEGIN
b_so_am(v_b_so_id, v_code, v_message);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('发货订单记录id：' || v_b_so_id ||
',调用发货订单am程序B_SO_AM发生异常:' ||
SQLERRM);
END;
END LOOP;
--生成发货订单后，更新订单接口xq_midso当前记录is_so为‘N’
UPDATE xq_midso xms
SET xms.is_so = 'N'
WHERE xms.id = v_list.id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('订单接口记录id：' || v_list.id ||
',生成发货订单发生异常:' || SQLERRM);
END;
END LOOP;
END;
----用于自动生成单据编号
CREATE OR REPLACE FUNCTION get_sequenceno(p_seqname IN VARCHAR2,
p_clientid IN NUMBER) RETURN VARCHAR2 AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_prefix VARCHAR2(30);
v_postfix VARCHAR2(30);
v_format VARCHAR2(120);
v_currentnext NUMBER(10);
v_id NUMBER(10);
v_lastdate DATE;
v_no VARCHAR2(255);
v_cycletype CHAR(1);
v_incrementno NUMBER(10);
v_sql VARCHAR2(400);
pctx plog.log_ctx := plog.init('Get_SequenceNo', plog.linfo);
/**
* 根据ad_table定义的ad_sequence的名字
* vFormat 中含有生成的序列的规则, 最终的编号规则是
* prefix + vFormat + postfix
* vFormat 中 yy/mm/dd 表示年，9999表示按日循环递增，0000表示无循环递增
* @param p_seqName 是 ad_sequence 表的name 字段的值
*/
BEGIN
SELECT id, nvl(prefix, ''), nvl(suffix, ''), vformat, currentnext,
to_date(to_char(lastdate), 'YYYYMMDD'), cycletype,
nvl(incrementno, 1)
INTO v_id, v_prefix, v_postfix, v_format, v_currentnext, v_lastdate,
v_cycletype, v_incrementno
FROM ad_sequence
WHERE NAME = upper(TRIM(p_seqname))
AND ad_client_id = p_clientid
FOR UPDATE;
IF v_cycletype = 'D' THEN
-- cycle by day
IF to_char(v_lastdate, 'yyyymmdd') <> to_char(SYSDATE, 'yyyymmdd') THEN
v_currentnext := 0;
END IF;
ELSIF v_cycletype = 'M' THEN
-- cycle by month
IF to_char(v_lastdate, 'yyyymm') <> to_char(SYSDATE, 'yyyymm') THEN
v_currentnext := 0;
END IF;
ELSIF v_cycletype = 'Y' THEN
-- cycle by year
IF to_char(v_lastdate, 'yyyy') <> to_char(SYSDATE, 'yyyy') THEN
v_currentnext := 0;
END IF;
END IF;
v_currentnext := v_currentnext + v_incrementno;
UPDATE ad_sequence
SET lastdate = to_number(to_char(SYSDATE, 'YYYYMMDD')),
currentnext = v_currentnext
WHERE id = v_id;
v_sql := 'select ' ||
REPLACE(v_format, '$nextval', ltrim(to_char(v_currentnext))) ||
' from dual ';
EXECUTE IMMEDIATE v_sql
INTO v_no;
COMMIT;
plog.info(pctx,
'p_seqName=' || p_seqname || ', CURRENTNEXT=' || v_currentnext ||
', v_lastdate=' || v_lastdate);
COMMIT;
RETURN v_prefix || v_no || v_postfix;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
plog.error(pctx,
'Error for Get_SequenceNo(' || p_seqname || ',' ||
p_clientid || '):code=' || SQLCODE || ', err=' || SQLERRM);
COMMIT;
raise_application_error(-20201, '无法生成名称为 ' || p_seqname || ' 的单据号!');
END;
---------------------------------------------------------------
报表
----0030525: 一、20180727-V1.0-凰艮项目20180717-云仓订单信息汇总表 4h
CREATE OR REPLACE PROCEDURE rp_o2oinfo_generate(p_pi_id NUMBER) IS
---------------------------------------------------
--Author:xy
-- Date:20180721
-- Description :
-- 查询单据日期在查询条件的单据日期范围内、 下单店仓与查询条件的店仓一致、并且已提交的、未结案、可用的云仓订单
---------------------------------------------------
v_userid NUMBER(10);
v_date VARCHAR2(80);
v_datebegin NUMBER(8); --开始日期
v_dateend NUMBER(8); --结束日期
v_c_store_ids r_tabid := r_tabid(); --下单店仓id集合
v_sql1 VARCHAR2(4000);
BEGIN
-- raise_application_error(-20201, p_pi_id);
EXECUTE IMMEDIATE ('truncate TABLE RP_O2OINFO');
--定义公共sql
v_sql1 := 'SELECT t.info
FROM ad_pinstance_para t
WHERE t.name = :name AND t.ad_pinstance_id = :p_pi_id';
SELECT t.modifierid
INTO v_userid
FROM ad_pinstance t
WHERE t.id = p_pi_id;
--依次获取界面查询条件参数
EXECUTE IMMEDIATE v_sql1
INTO v_date
USING 'BILLDATE', p_pi_id;
IF v_date IS NULL THEN
--如开始日期和结束日期都没有选择，
raise_application_error(-20201, '请选择单据日期范围！');
ELSE
IF instr(v_date, '>=') > 0 THEN
--如只选择了开始日期，则结束日期默认为当前时间
--注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
v_datebegin := substr(v_date, 13, 8);
--结束日期默认为当前日期
v_dateend := to_char(SYSDATE, 'yyyymmdd');
ELSIF instr(v_date, '<=') > 0 THEN
--如只选择了结束日期，则开始日期默认18400101
--注意这里是不是从9开始，取决于日期参数定义，例如日期参数名定义为DATE，则此处为9，如定义为BILLDATE，此处为13，以此类推
v_dateend := substr(v_date, 13, 8);
v_datebegin := 18400101;
ELSE
v_datebegin := substr(v_date, 21, 8);
v_dateend := substr(v_date, 34, 8);
END IF;
END IF;
--获取下单店仓id集合
EXECUTE IMMEDIATE v_sql1
INTO v_sql1
USING 'C_STORE_ID', p_pi_id;
v_c_store_ids := f_fast_table(v_sql1);
IF v_c_store_ids.COUNT = 0 THEN
v_sql1 := 'SELECT CS.ID FROM C_STORE CS WHERE CS.ISACTIVE = ''Y''';
v_c_store_ids := f_fast_table(v_sql1);
END IF;
--下单次数：单据日期等于行定义单据日期，下单店仓等于行定义店铺，已提交的可用的云仓订单的个数
INSERT INTO rp_o2oinfo
(id, ad_client_id, ad_org_id, billdate, yearmonth, c_store_id, ownerid,
isactive, ordernum, ad_pi_id)
SELECT get_sequences('RP_O2OINFO'), os.ad_client_id, os.ad_org_id,
os.billdate, substr(os.billdate, 1, 6), os.c_store_id, os.ownerid,
'Y', COUNT(1), p_pi_id
FROM o2o_so os, TABLE(v_c_store_ids) vs
WHERE os.isactive = 'Y'
AND os.status = 2
AND os.close_status = 1
AND os.billdate BETWEEN v_datebegin AND v_dateend
AND os.c_store_id = vs.id
GROUP BY os.ad_client_id, os.ad_org_id, os.billdate, os.c_store_id,
os.ownerid;
--发货次数：发货日期等于行定义的单据日期，发货店仓等于行定义店铺，发货状态为已提交的云仓订单的个数
--快递费用：发货时间的日期等于行定义单据日期，发货店仓等于行定义店铺，发货状态为已提交的云仓订单中快递费用/元的和
MERGE INTO rp_o2oinfo rp
USING (SELECT os.dateoutin, os.c_orig_id, COUNT(os.id) AS cnt,
SUM(os.deliverycosts) AS tot_exp_fee
FROM o2o_so os, TABLE(v_c_store_ids) vs
WHERE os.isactive = 'Y'
AND os.out_status = 2
AND os.status = 2
AND os.close_status = 1
AND os.dateoutin BETWEEN v_datebegin AND v_dateend
AND os.c_orig_id = vs.id
GROUP BY os.dateoutin, os.c_orig_id) tp
ON (rp.c_store_id = tp.c_orig_id AND rp.billdate = tp.dateoutin)
WHEN MATCHED THEN
UPDATE
SET rp.deliverynum = tp.cnt, rp.express_fee = tp.tot_exp_fee;
--客诉：单据日期等于行定义单据日期，发货店仓等于行定义店铺，已提交的客诉申请单的的个数。
MERGE INTO rp_o2oinfo rp
USING (SELECT cc.billdate, cc.c_orig_id, COUNT(cc.id) AS cnt
FROM c_complain cc, TABLE(v_c_store_ids) vs
WHERE cc.status = 2
AND cc.billdate BETWEEN v_datebegin AND v_dateend
AND cc.c_orig_id = vs.id
GROUP BY cc.billdate, cc.c_orig_id) tp
ON (rp.c_store_id = tp.c_orig_id AND rp.billdate = tp.billdate)
WHEN MATCHED THEN
UPDATE
SET rp.complain_num = tp.cnt;
--拒单次数：变更时间的日期等于行定义单据日期，店仓等于行定义店铺，
--重新指派原因或者退回原因不为空的表【派单日志O2O_SOSPLIT_LOG】的记录的条数。
MERGE INTO rp_o2oinfo rp
USING (SELECT to_number(to_char(osl.changetime, 'YYYYMMDD')) AS changedate,
osl.c_store_id, COUNT(osl.id) AS cnt
FROM o2o_sosplit_log osl, TABLE(v_c_store_ids) vs
WHERE to_number(to_char(osl.changetime, 'YYYYMMDD')) BETWEEN
v_datebegin AND v_dateend
AND (osl.o2o_assignorig_reason_id IS NOT NULL OR
osl.o2o_back_reason_id IS NOT NULL)
AND osl.c_store_id = vs.id
GROUP BY to_number(to_char(osl.changetime, 'YYYYMMDD')),
osl.c_store_id) tp
ON (rp.billdate = tp.changedate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.reject_ordernum = tp.cnt;
--次品拒单：变更时间的日期等于行定义单据日期，店仓等于行定义店铺，
--退回原因O2O_BACK_REASON_ID等于表【退回原因O2O_BACK_REASON】中描述=“货品残次”；表【派单日志O2O_SOSPLIT_LOG】的记录的条数。
MERGE INTO rp_o2oinfo rp
USING (SELECT to_number(to_char(osl.changetime, 'YYYYMMDD')) AS changedate,
osl.c_store_id, COUNT(osl.id) AS cnt
FROM o2o_sosplit_log osl, o2o_back_reason obr, TABLE(v_c_store_ids) vs
WHERE to_number(to_char(osl.changetime, 'YYYYMMDD')) BETWEEN
v_datebegin AND v_dateend
AND obr.id = osl.o2o_back_reason_id
AND obr.NAME = '货品残次'
AND osl.c_store_id = vs.id
GROUP BY to_number(to_char(osl.changetime, 'YYYYMMDD')),
osl.c_store_id) tp
ON (rp.billdate = tp.changedate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.reject_defectnum = tp.cnt;
--无实货拒单：变更时间的日期等于行定义单据日期，店仓等于行定义店铺，
--退回原因O2O_BACK_REASON_ID等于表【退回原因O2O_BACK_REASON】中描述=“库存不足”；表【派单日志O2O_SOSPLIT_LOG】的记录的条数。
MERGE INTO rp_o2oinfo rp
USING (SELECT to_number(to_char(osl.changetime, 'YYYYMMDD')) AS changedate,
osl.c_store_id, COUNT(osl.id) AS cnt
FROM o2o_sosplit_log osl, o2o_back_reason obr, TABLE(v_c_store_ids) vs
WHERE to_number(to_char(osl.changetime, 'YYYYMMDD')) BETWEEN
v_datebegin AND v_dateend
AND obr.id = osl.o2o_back_reason_id
AND obr.NAME = '库存不足'
AND osl.c_store_id = vs.id
GROUP BY to_number(to_char(osl.changetime, 'YYYYMMDD')),
osl.c_store_id) tp
ON (rp.billdate = tp.changedate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.rejet_nogoods_num = tp.cnt;
--超时转单次数：变更时间的日期等于行定义单据日期，店仓等于行定义店铺，
--退回原因O2O_BACK_REASON_ID等于表【退回原因O2O_BACK_REASON】中描述=“系统退回”；表【派单日志O2O_SOSPLIT_LOG】的记录的条数。
MERGE INTO rp_o2oinfo rp
USING (SELECT to_number(to_char(osl.changetime, 'YYYYMMDD')) AS changedate,
osl.c_store_id, COUNT(osl.id) AS cnt
FROM o2o_sosplit_log osl, o2o_back_reason obr, TABLE(v_c_store_ids) vs
WHERE to_number(to_char(osl.changetime, 'YYYYMMDD')) BETWEEN
v_datebegin AND v_dateend
AND obr.id = osl.o2o_back_reason_id
AND obr.NAME = '系统退回'
AND osl.c_store_id = vs.id
GROUP BY to_number(to_char(osl.changetime, 'YYYYMMDD')),
osl.c_store_id) tp
ON (rp.billdate = tp.changedate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.overtime_ordernum = tp.cnt;
--所得奖励：激励时间的日期等于行定义单据日期，店铺等于行定义店铺，表【店铺激励明细表】中激励金额的和。
MERGE INTO rp_o2oinfo rp
USING (SELECT to_number(to_char(cso.supdate, 'YYYYMMDD')) AS supdate,
cso.c_store_id, SUM(cso.supamt) AS tot_supamt
FROM c_storesup_o2o cso, TABLE(v_c_store_ids) vs
WHERE to_number(to_char(cso.supdate, 'YYYYMMDD')) BETWEEN v_datebegin AND
v_dateend
AND cso.c_store_id = vs.id
GROUP BY to_number(to_char(cso.supdate, 'YYYYMMDD')), cso.c_store_id) tp
ON (rp.billdate = tp.supdate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.reward = tp.tot_supamt;
--信用分：变动日期等于行定义单据日期，店仓等于行定义店仓，表【店仓信用流水账】中信用额度的和。
MERGE INTO rp_o2oinfo rp
USING (SELECT fsf.c_store_id, fsf.changedate,
SUM(fsf.credit_quota) AS tot_cred_quota
FROM fa_storecredit_ftp fsf, TABLE(v_c_store_ids) vs
WHERE fsf.changedate BETWEEN v_datebegin AND v_dateend
AND fsf.c_store_id = vs.id
GROUP BY fsf.changedate, fsf.c_store_id) tp
ON (rp.billdate = tp.changedate AND rp.c_store_id = tp.c_store_id)
WHEN MATCHED THEN
UPDATE
SET rp.credit_score = tp.tot_cred_quota;
END;
----------------------------------------------------------------------------
--美邦：期货销售合同中的’预配‘动作定义
CREATE OR REPLACE PROCEDURE b_perallot_gen(p_user_id IN NUMBER,
p_query IN VARCHAR2,
r_code OUT NUMBER,
r_message OUT VARCHAR2) AS
---------------------------------------------------------
--author: xy
--date: 20180725
--并发控制。
--增加控制：如果单据未提交，不允许。
--增加控制：如果系统中存在期货合同编号为本单的期货订货单或期货配差单，不允许。
--增加控制：如果期货销售合同在中间表中已经存在，不允许再次传入(用合同编号判断)。
--将本单期货销售合同头表信息传入中间表[UNI_FIRSO]、期货销售合同明细信息传入中间表[UNI_FIRSOITEM]
--传入中间表后，更新单据的[预配是否传入中间表]为Y
---------------------------------------------------------
--声明用于从p_query解析参数获得单据ID的相关记录和变量
TYPE t_queryobj IS RECORD(
"table" VARCHAR2(255),
query VARCHAR2(32676),
id VARCHAR2(10));
v_queryobj t_queryobj;
TYPE t_selection IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
v_selection t_selection;
st_xml VARCHAR2(32676);
v_xml xmltype;
p_id NUMBER(10); --单据ID
--其他变量定义
v_status b_fir_so.status%TYPE; --单据状态
v_docno b_fir_so.docno%TYPE; --单据编号
v_cnt NUMBER(10);
v_cnt1 NUMBER(10);
BEGIN
--从p_query解析参数
st_xml := '<data>' || p_query || '</data>';
v_xml := xmltype(st_xml);
SELECT extractvalue(VALUE(t), '/data/table'),
extractvalue(VALUE(t), '/data/query'),
extractvalue(VALUE(t), '/data/id')
INTO v_queryobj
FROM TABLE(xmlsequence(extract(v_xml, '/data'))) t;
SELECT extractvalue(VALUE(t), '/selection')
BULK COLLECT
INTO v_selection
FROM TABLE(xmlsequence(extract(v_xml, '/data/selection'))) t;
p_id := v_queryobj.id;
--end 解析参数
--并发控制
BEGIN
EXECUTE IMMEDIATE 'select 1 from b_fir_so t where t.id=' || p_id ||
' for update nowait';
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20201, SQLERRM);
END;
--获取提交状态,单据编号
SELECT bfs.status, bfs.docno
INTO v_status, v_docno
FROM b_fir_so bfs
WHERE bfs.id = p_id;
--如果单据未提交，不允许
IF v_status = 1 THEN
raise_application_error(-20201, '单据未提交，不允许！');
END IF;
--如果系统中存在期货合同编号为本单的期货订货单或期货配差单，不允许
SELECT nvl(COUNT(1), 0)
INTO v_cnt
FROM b_fwdso bfw
WHERE bfw.b_fir_so_id = p_id;
SELECT nvl(COUNT(1), 0)
INTO v_cnt1
FROM b_fwdtocan bfw
WHERE bfw.b_fir_so_id = p_id;
IF v_cnt <> 0 OR v_cnt1 <> 0 THEN
raise_application_error(-20201, '系统中存在期货合同编号为本单的期货订货单或期货配差单，不允许！');
END IF;
--如果期货销售合同在中间表中已经存在，不允许再次传入(用合同编号判断)
SELECT nvl(COUNT(id), 0)
INTO v_cnt
FROM uni_firso uf
WHERE uf.code = v_docno;
IF v_cnt <> 0 THEN
raise_application_error(-20201,
'期货销售合同:' || v_docno || '在中间表中已经存在，不允许再次传入！');
END IF;
--将本单期货销售合同头表信息传入中间表[UNI_FIRSO]、期货销售合同明细信息传入中间表[UNI_FIRSOITEM]
INSERT INTO uni_firso
(id, ad_client_id, ad_org_id, code, vender_code, vendee_code, doc_date,
shop_code, rcv_wareh_code, is_assign_wareh, assign_wareh_code,
order_qty, audit_date, require_date, brand_code, remark, ediflag,
ownerid, modifierid, creationdate, modifieddate, isactive)
SELECT bfs.id, bfs.ad_client_id, bfs.ad_org_id, bfs.docno, gc.code,
gc1.code, bfs.billdate, cs.code,
decode(bfs.protype, 1, cs1.code, 2, cs2.code),
decode(bfs.is_origstore, 'Y', 'T', 'N', 'F'), cs3.code,
bfs.tot_qty, bfs.statustime, bfs.perdate, md.attribcode,
bfs.description, 80, p_user_id, p_user_id, SYSDATE, SYSDATE, 'Y'
FROM b_fir_so bfs
LEFT JOIN g_company gc
ON (gc.id = bfs.g_company_id)
LEFT JOIN g_company gc1
ON (gc1.id = bfs.g_destpany_id)
LEFT JOIN c_store cs
ON (cs.id = bfs.c_dest_id)
LEFT JOIN c_store cs1
ON (cs1.id = cs.c_spstore_id)
LEFT JOIN c_store cs2
ON (cs2.id = cs.c_flstore_id)
LEFT JOIN c_store cs3
ON (cs3.id = bfs.c_store_id)
LEFT JOIN m_dim md
ON (md.id = bfs.m_dim1_id)
WHERE bfs.id = p_id;
INSERT INTO uni_firsoitem
(id, ad_client_id, ad_org_id, fuc_code, prod_code, order_qty, ediflag,
ownerid, modifierid, creationdate, modifieddate, isactive)
SELECT bfi.id, bfi.ad_client_id, bfi.ad_org_id, v_docno, mpa.no, bfi.qty,
80, p_user_id, p_user_id, SYSDATE, SYSDATE, 'Y'
FROM b_fir_soitem bfi
JOIN m_product_alias mpa
ON (mpa.id = bfi.m_productalias_id)
WHERE bfi.b_fir_so_id = p_id;
--传入中间表后，更新单据的[预配是否传入中间表]
UPDATE b_fir_so bfs
SET bfs.is_pretouni = 'Y'
WHERE bfs.id = p_id;
r_code := 1;
r_message := '预配成功！！';
END;
---------------------------------------------------------------------------------
--从界面端clob字段获取对应sql语句
CREATE OR REPLACE FUNCTION get_fitler_sql(p_str IN CLOB) RETURN CLOB IS
v_xml xmltype;
myresult CLOB;
BEGIN
--edit by robin 解决超过4000个字符问题
if p_str is null then
return null;
end if;
begin
v_xml:=xmltype(p_str);
SELECT extractvalue(VALUE(t), '/filter/sql')
INTO myresult
FROM TABLE(xmlsequence(extract(v_xml, '/filter'))) t;
exception
when others then
myresult := clobTransXml(clobSubStr(p_str, '<sql>', '</sql>'));
end;
RETURN myresult;
END get_fitler_sql;
----------------------------------------------------------------------------
--获取对应记录id集合
create or replace function f_fast_table(v_sql in varchar2) return r_tabid as
v_test r_tabid := r_tabid();
type t_type1 is table of number(10) index by binary_integer;
p_id t_type1;
begin
execute immediate v_sql bulk collect into p_id;
if p_id.count != 0 then
for i in 1 .. p_id.last loop
v_test.extend();
v_test(v_test.count) := r_id(p_id(i));
end loop;
else
v_test.extend();
v_test(1) := r_id(0);
end if;
return v_test;
end ;
-------------------------------------------------------------------------
--获取指定单据编号
CREATE OR REPLACE FUNCTION get_sequenceno(p_seqname IN VARCHAR2,
p_clientid IN NUMBER) RETURN VARCHAR2 AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_prefix VARCHAR2(30);
v_postfix VARCHAR2(30);
v_format VARCHAR2(120);
v_currentnext NUMBER(10);
v_id NUMBER(10);
v_lastdate DATE;
v_no VARCHAR2(255);
v_cycletype CHAR(1);
v_incrementno NUMBER(10);
v_sql VARCHAR2(400);
pctx plog.log_ctx := plog.init('Get_SequenceNo', plog.linfo);
/**
* 根据ad_table定义的ad_sequence的名字
* vFormat 中含有生成的序列的规则, 最终的编号规则是
* prefix + vFormat + postfix
* vFormat 中 yy/mm/dd 表示年，9999表示按日循环递增，0000表示无循环递增
* @param p_seqName 是 ad_sequence 表的name 字段的值
*/
BEGIN
SELECT id, nvl(prefix, ''), nvl(suffix, ''), vformat, currentnext,
to_date(to_char(lastdate), 'YYYYMMDD'), cycletype,
nvl(incrementno, 1)
INTO v_id, v_prefix, v_postfix, v_format, v_currentnext, v_lastdate,
v_cycletype, v_incrementno
FROM ad_sequence
WHERE NAME = upper(TRIM(p_seqname))
AND ad_client_id = p_clientid
FOR UPDATE;
IF v_cycletype = 'D' THEN
-- cycle by day
IF to_char(v_lastdate, 'yyyymmdd') <> to_char(SYSDATE, 'yyyymmdd') THEN
v_currentnext := 0;
END IF;
ELSIF v_cycletype = 'M' THEN
-- cycle by month
IF to_char(v_lastdate, 'yyyymm') <> to_char(SYSDATE, 'yyyymm') THEN
v_currentnext := 0;
END IF;
ELSIF v_cycletype = 'Y' THEN
-- cycle by year
IF to_char(v_lastdate, 'yyyy') <> to_char(SYSDATE, 'yyyy') THEN
v_currentnext := 0;
END IF;
END IF;
v_currentnext := v_currentnext + v_incrementno;
UPDATE ad_sequence
SET lastdate = to_number(to_char(SYSDATE, 'YYYYMMDD')),
currentnext = v_currentnext
WHERE id = v_id;
v_sql := 'select ' ||
REPLACE(v_format, '$nextval', ltrim(to_char(v_currentnext))) ||
' from dual ';
EXECUTE IMMEDIATE v_sql
INTO v_no;
COMMIT;
plog.info(pctx,
'p_seqName=' || p_seqname || ', CURRENTNEXT=' || v_currentnext ||
', v_lastdate=' || v_lastdate);
COMMIT;
RETURN v_prefix || v_no || v_postfix;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
plog.error(pctx,
'Error for Get_SequenceNo(' || p_seqname || ',' ||
p_clientid || '):code=' || SQLCODE || ', err=' || SQLERRM);
COMMIT;
raise_application_error(-20201, '无法生成名称为 ' || p_seqname || ' 的单据号!');
END;
------
（美邦）存储过程：MATMULTI_INVENTORY
--弹出一个对话框，并且在关闭时刷新后面的那张网页
SELECT to_char(id)
INTO t_id
FROM ad_table
WHERE NAME = 'M_MATMULTI_INVENTORY';
r_code := 5;
r_message := 'showDialog("/html/nds/object/object.jsp?table=' || t_id ||
'&fixedcolumns=&id=' || v_m_matmulti_inventory_id ||
'",940, 530,true)';
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 输入的数据已存在ID: SELECT MAX(ID) FROM AD_COLUMN; 根据max（ID）设置序列SEQ_AD_COLUMN的下一个值 -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
