17,377
社区成员
发帖
与我相关
我的任务
分享
select ck_code,bom_code,bom_name,unit,sum(b_je1),sum(b_je2),sum(b_je3),sum(b_je4),sum(b_je5),sum(b_je6),
sum(b_je7),sum(b_je8),sum(b_je9),sum(b_je10),sum(b_je11),sum(b_je12)
from
(
select t.*,decode(order_type,'R',fssl,'C',-fssl) b_sl,
CASE WHEN fsrq between TO_TIMESTAMP('01-1月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-1月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je1,
CASE WHEN fsrq between TO_TIMESTAMP('01-2月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('29-2月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je2,
CASE WHEN fsrq between TO_TIMESTAMP('01-3月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-3月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je3,
CASE WHEN fsrq between TO_TIMESTAMP('01-4月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-4月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je4,
CASE WHEN fsrq between TO_TIMESTAMP('01-5月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-5月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je5,
CASE WHEN fsrq between TO_TIMESTAMP('01-6月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-6月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je6,
CASE WHEN fsrq between TO_TIMESTAMP('01-7月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-7月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je7,
CASE WHEN fsrq between TO_TIMESTAMP('01-8月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-8月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je8,
CASE WHEN fsrq between TO_TIMESTAMP('01-9月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-9月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je9,
CASE WHEN fsrq between TO_TIMESTAMP('01-10月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-10月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je10,
CASE WHEN fsrq between TO_TIMESTAMP('01-11月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-11月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je11,
CASE WHEN fsrq between TO_TIMESTAMP('01-12月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-12月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN decode(order_type,'R',je,'C',-je)
END b_je12,
CASE
WHEN fsrq between TO_TIMESTAMP('01-1月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-1月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 1
WHEN fsrq between TO_TIMESTAMP('01-2月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('29-2月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 2
WHEN fsrq between TO_TIMESTAMP('01-3月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-3月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 3
WHEN fsrq between TO_TIMESTAMP('01-4月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-4月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 4
WHEN fsrq between TO_TIMESTAMP('01-5月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-5月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 5
WHEN fsrq between TO_TIMESTAMP('01-6月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-6月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 6
WHEN fsrq between TO_TIMESTAMP('01-7月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-7月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 7
WHEN fsrq between TO_TIMESTAMP('01-8月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-8月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 8
WHEN fsrq between TO_TIMESTAMP('01-9月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-9月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 9
WHEN fsrq between TO_TIMESTAMP('01-10月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-10月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 10
WHEN fsrq between TO_TIMESTAMP('01-11月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('30-11月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 11
WHEN fsrq between TO_TIMESTAMP('01-12月 -12 12.00.00.838000 上午','DD-Mon -RR HH.MI.SS.FF am TZH')
and TO_TIMESTAMP('31-12月 -12 11.59.59.838000 下午','DD-Mon -RR HH.MI.SS.FF am TZH') THEN 12
END num_month
from test_crmx t
) m
group by ck_code,bom_code,bom_name,unit
CREATE TABLE "XXZWZX"."TEST_CRMX" --出入明细表
( "CK_CODE" NUMBER NOT NULL ENABLE,
"BOM_CODE" NUMBER NOT NULL ENABLE,
"BOM_NAME" VARCHAR2(50),
"FSSL" NUMBER,
"UNIT" VARCHAR2(10),
"PRICE" NUMBER,
"JE" NUMBER,
"FSRQ" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
NOT NULL ENABLE,
"ORDER_TYPE" CHAR(1) NOT NULL ENABLE,
CONSTRAINT "PK_T_CRMX_FSRQ" PRIMARY KEY ("CK_CODE", "FSRQ", "ORDER_TYPE", "BOM_CODE")
)
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."CK_CODE" IS '仓库';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."BOM_CODE" IS '物料代码';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."BOM_NAME" IS '物料名称';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."FSSL" IS '数量';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."UNIT" IS '单位';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."PRICE" IS '单价';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."JE" IS '金额';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."FSRQ" IS '发生时间';
COMMENT ON COLUMN "XXZWZX"."TEST_CRMX"."ORDER_TYPE" IS '单据类型';
COMMENT ON TABLE "XXZWZX"."TEST_CRMX" IS '出入库明细表';
CREATE TABLE "XXZWZX"."TEST_KC" --实时库存表
( "CK_CODE" NUMBER NOT NULL ENABLE,
"BOM_CODE" NUMBER NOT NULL ENABLE,
"BOM_NAME" VARCHAR2(50),
"SL" NUMBER,
"UNIT" VARCHAR2(10),
CONSTRAINT "PK_TEST_KC_CK_BOM" PRIMARY KEY ("CK_CODE", "BOM_CODE")
)
COMMENT ON COLUMN "XXZWZX"."TEST_KC"."CK_CODE" IS '仓库';
COMMENT ON COLUMN "XXZWZX"."TEST_KC"."BOM_CODE" IS '物料代码';
COMMENT ON COLUMN "XXZWZX"."TEST_KC"."BOM_NAME" IS '物料名称';
COMMENT ON COLUMN "XXZWZX"."TEST_KC"."SL" IS '库存数量';
COMMENT ON COLUMN "XXZWZX"."TEST_KC"."UNIT" IS '单位';
COMMENT ON TABLE "XXZWZX"."TEST_KC" IS '仓库库存表';