javasrc/sql/HEAD
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
単語検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
[[javasrc/sql]]
#highlighter(MySQL,number){{
CREATE OR REPLACE PACKAGE CPA_COS_ALLOC_PRNT_RE
/********************************************************...
<Synopsis>
原価帳票情報作成パッケージ
<Remarks>
指定した計算タイプ、年月などの原価計算結果から原価帳票...
下記原価帳票に対する情報を作成する。
原価計算表
入庫明細表
<History>
2017/10/05 K.Nonoshita 新規作成
2017/11/01 S.Yamamoto 単位の出力変更(費目行、小計と総...
*********************************************************...
IS
c$project_nm CONSTANT VARCHAR2(20) := '原価帳票情報作成';
c$project_cd CONSTANT VARCHAR2(100) := 'CPA_COS_ALLOC_P...
-- 帳票NO
g$PRINT_NO COS_GT_GENKA.PRNT_NO%TYPE;
g$SEQ COS_GT_GENKA.SEQ%TYPE := 0;
g$vCOS_GT_ATENKAI_UPD COS_GT_GENKA%ROWTYPE; ...
g$MNG_DPT_CD COS_GT_GENKA.MNG_DPT_CD%TYPE := NU...
TYPE tCOS_GW_GENKA_HIS IS TABLE OF COS_GW_GENKA_HIS%ROWT...
TYPE tCOS_GT_GENKA IS TABLE OF COS_GT_GENKA%ROWTYPE;
g$vCOS_GT_GENKA_ITM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ITM_TMP COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ITM_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_EXPNS COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_EXPNS_GRP COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_PP_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_PROD_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_DIRECT_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_INDIRECT_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ALEVEL0_OPT COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ALVL0_OPT_SUM COS_GT_GENKA%ROWTYPE; ...
-- @2017/11/13 S.Nonoshita MOD START
p$UNIT_NM CM_UNIT.UNIT_NM%TYPE := NULL;
-- @2017/11/13 S.Nonoshita MOD END
-- 原価計算表用情報
CURSOR curCTG_GENKA(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M4.MST_RMRKS AS PAR_CTG_NM
,M2.P_CTG_CD AS CHD_CTG_CD
,M5.CTG_NM AS CHD_CTG_NM
,T3.EXPNS_CD
,M3.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M8.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS VAR_NUM
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(M3.VAR_FLG, 1,...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS FIX_NUM
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M4.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_C...
LEFT OUTER JOIN CM_ITEM_CTG M11 ON (M7.CTG_D = M11.ITEM...
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UN...
-- @2017/11/01 S.Yamamoto DEL END
LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND M1...
LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AND ...
LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND M1...
LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO, ...
LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10.C...
INNER JOIN COS_GW_ATENKAI W1 ON (W1.CALC_NO = T2.CALC...
WHERE 0 = 0
AND T1.REC_TYP = '0'
AND T3.EXPNS_CD IS NOT NULL
AND M1.P_CTG_CD = p$P_CTG_CD
AND M2.P_CTG_CD = p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, M3.PRINT_SORT ASC, M3.VAR_FLG;
-- 原価計算表用情報(品郡単位)
CURSOR curCTG_GENKA_ITM(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M4.MST_RMRKS AS PAR_CTG_NM
,M2.P_CTG_CD AS CHD_CTG_CD
,M5.CTG_NM AS CHD_CTG_NM
,T3.EXPNS_CD
,M11.ITEM_CTG_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
,M8.UNIT_NM AS UNIT_NM
-- @2017/11/06 S.Nonoshita MOD START
-- ,DECODE(M3.VAR_FLG, 1, T2.ACT_QTY, 0) ...
-- @2017/11/13 S.Nonoshita MOD START
-- ,DECODE(W2.CALC_NO, NULL, 0, T2.ACT_QTY...
,DECODE(W2.CALC_NO, NULL, 0, T2.ACT_QTY *...
-- @2017/11/13 S.Nonoshita MOD END
-- @2017/11/06 S.Nonoshita MOD END
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
-- @2017/11/06 S.Nonoshita MOD START
-- ,DECODE(M3.VAR_FLG, 1, 0, T2.ACT_QTY) AS FIX_NUM
,0 AS FIX_NUM
-- @2017/11/06 S.Nonoshita MOD END
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,M9.UNIT_CD
,NVL(M9.UNIT_WT,0) AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M4.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_C...
LEFT OUTER JOIN CM_ITEM_CTG M11 ON (M7.CTG_D = M11.ITEM...
LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UNIT...
-- @2017/11/13 S.Nonoshita MOD START
LEFT OUTER JOIN CM_ITM_UNIT M9 ON (M7.ITM_CD = M9.ITM_C...
-- @2017/11/13 S.Nonoshita MOD END
-- @2017/11/06 S.Nonoshita MOD START
-- LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND ...
-- LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AN...
-- LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND ...
-- LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO...
-- LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10...
LEFT OUTER JOIN COS_GW_ATENKAI W2 ON ( W2.CALC_NO = T1.C...
-- @2017/11/06 S.Nonoshita MOD END
WHERE 0 = 0
AND T1.REC_TYP = '0'
-- AND T2.LVL > 0
AND EXISTS ( SELECT * FROM COS_GW_ATENKAI W1 WHERE W1.CA...
AND M2.P_CTG_CD IS NOT NULL
AND M2.P_CTG_CD <> p$P_CTG_CD
AND M1.P_CTG_CD = p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY M7.CTG_D,T2.REC_GRP,T2.REC_NO ASC ;
vcurCTG_GENKA curCTG_GENKA%ROWTYPE;
vcurCTG_GENKA_ITM curCTG_GENKA_ITM%ROWTYPE;
-- <未入庫仕掛品>
CURSOR curNON_INPT_ITM(
p$REC_TYP IN VARCHAR2,
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M4.CTG_GRP_CD AS CTG_GRP_CD
,M4.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M3.MST_RMRKS AS PAR_CTG_NM
,NULL AS CHD_CTG_CD
,NULL AS CHD_CTG_NM
,T2.EXPNS_CD
,M2.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M6.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, ...
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, '3',T2.ACT_COS...
,CASE WHEN M2.VAR_FLG = 0 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS VAR_COST
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, '3',T1.ACT_...
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, '3',T2.ACT_...
,CASE WHEN M2.VAR_FLG = 1 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T1.REC_GRP
,0 AS REC_NO
,0 AS LVL
,M2.MST_RMRKS
,M2.PRINT_SORT
,M2.VAR_FLG
,M2.INDIRECT_FLG
,MAX(M2.VAR_FLG) OVER(PARTITION BY M2.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ASHUKEI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
INNER JOIN GM_HIMOKU M2 ON (T2.EXPNS_CD = M2.EXP...
LEFT OUTER JOIN GM_HINGUN M3 ON (M1.P_CTG_CD = M3.CTG...
LEFT OUTER JOIN GM_HINGUNG M4 ON (M3.CTG_GRP_CD = M4.C...
LEFT OUTER JOIN GM_HINMO M5 ON (T1.ITM_CD = M5.ITM_C...
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M6 ON (M5.UNIT_CD = M6.UN...
-- @2017/11/01 S.Yamamoto DEL END
INNER JOIN COS_GW_GENKA_HIS W1 ON (W1.CALC_NO = T1.CA...
WHERE 0 = 0
AND T1.REC_TYP = p$REC_TYP
AND M1.P_CTG_CD LIKE p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, T1.ITM_CD;
vcurNON_INPT_ITM curNON_INPT_ITM%ROWTYPE;
-- <中間体後工程払出>
CURSOR curRC_INPT(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M2.P_CTG_CD AS PAR_CTG_CD
,M5.MST_RMRKS AS PAR_CTG_NM
,M1.P_CTG_CD AS CHD_CTG_CD
,M4.CTG_NM AS CHD_CTG_NM
,NULL AS EXPNS_CD
,M4.CTG_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M8.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS VAR_NUM
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(M3.VAR_FLG, 1,...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS FIX_NUM
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M5.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_CD)
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UN...
-- @2017/11/01 S.Yamamoto DEL END
LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND M1...
LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AND ...
LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND M1...
LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO, ...
LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10.C...
INNER JOIN COS_GW_ATENKAI W1 ON (W1.CALC_NO = T2.CALC...
WHERE 0 = 0
AND T1.REC_TYP = '0'
AND M2.P_CTG_CD LIKE 'RC%'
AND M2.P_CTG_CD = p$P_CTG_CD
AND M2.P_CTG_CD <> M1.P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, M3.PRINT_SORT ASC, M3.VAR_FLG;
vcurRC_INPT curRC_INPT%ROWTYPE;
-- <系内仕掛品>
CURSOR curALEVEL0_OPT(
p$VAR_FLG IN CHAR,
p$REC_TYP IN VARCHAR2,
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M4.CTG_GRP_CD AS CTG_GRP_CD
,M4.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M3.MST_RMRKS AS PAR_CTG_NM
,NULL AS CHD_CTG_CD
,NULL AS CHD_CTG_NM
,T2.EXPNS_CD
,M2.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M6.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, ...
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, 'Q',T2.ACT_COS...
,CASE WHEN M2.VAR_FLG = 0 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS VAR_COST
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, 'Q',T1.ACT...
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, 'Q',T2.ACT...
,CASE WHEN M2.VAR_FLG = 1 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T1.REC_GRP
,0 AS REC_NO
,0 AS LVL
,M2.MST_RMRKS
,M2.PRINT_SORT
,M2.VAR_FLG
,M2.INDIRECT_FLG
,MAX(M2.VAR_FLG) OVER(PARTITION BY M2.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ASHUKEI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
INNER JOIN GM_HIMOKU M2 ON (T2.EXPNS_CD = M2.EXP...
LEFT OUTER JOIN GM_HINGUN M3 ON (M1.P_CTG_CD = M3.CTG...
LEFT OUTER JOIN GM_HINGUNG M4 ON (M3.CTG_GRP_CD = M4.C...
LEFT OUTER JOIN CM_HINMO M5 ON (T1.ITM_CD = M5.ITM_CD)
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M6 ON (M5.UNIT_CD = M6.UN...
-- @2017/11/01 S.Yamamoto DEL END
INNER JOIN COS_GW_GENKA_HIS W1 ON (W1.CALC_NO = T1.CA...
WHERE 0 = 0
AND M2.VAR_FLG = p$VAR_FLG
AND T1.REC_TYP = p$REC_TYP
AND M1.P_CTG_CD LIKE p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
;
vcurALEVEL0_OPT curALEVEL0_OPT%ROWTYPE;
PROCEDURE CREATE_PRNT_DAT(-- 帳票出力処理
p$RC OUT VARCHAR2,
p$PRNT_NO OUT VARCHAR2
) ;
/********************************************************...
<Synopsis>
帳票出力処理
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
FUNCTION IS_OPEN_TREE (
p$CALC_NO IN GT_ATENKAI.CALC_NO%TYPE,
p$REC_NO IN GT_ATENKAI.REC_NO%TYPE,
p$REC_GRP IN GT_ATENKAI.REC_GRP%TYPE,
p$LVL IN GT_ATENKAI.LVL%TYPE
) RETURN BOOLEAN ; -- 展開するかどうか
/********************************************************...
<Synopsis>
帳票出力処理
<Remarks>
<Arguments>
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXEC_GENKA_PRNT(
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
)
RETURN VARCHAR2; -- 原価帳票表情報作成
/********************************************************...
<Synopsis>
原価帳票表情報作成
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
VARCHAR2
*********************************************************...
FUNCTION IS_OUTPUT_EXPNS (
p$EXPNS_CD IN GM_HIMOKU.EXPNS_CD%TYPE
) RETURN BOOLEAN; -- 出力対象の費目かどうか
/********************************************************...
<Synopsis>
出力対象の費目かどうか
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION OPTION_GENKA_PRNT(
p$PRNT_SORT IN NUMBER,
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
)
RETURN VARCHAR2; -- 生産部門集計以降のデータ設定
/********************************************************...
<Synopsis>
生産部門集計以降のデータ設定
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
PROCEDURE OPTION_GENKA_VALUE(
p$GENKA_INDEX IN NUMBER,
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
); -- 生産量 入庫量 売上金額 売上原価
/********************************************************...
<Synopsis>
生産量 入庫量 売上金額 売上原価
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXPNS_SUM(
d$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE,
s$curCTG_GENKA IN curCTG_GENKA%ROWTYPE
)
RETURN COS_GT_GENKA%ROWTYPE; -- 左から右の足し算を行う。
/********************************************************...
<Synopsis>
費目に関する数量に対してのサマリー
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXPNS_MINUS(
d$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE,
s$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE
)
RETURN COS_GT_GENKA%ROWTYPE; -- 左から右の引き算を行う。
/********************************************************...
<Synopsis>
費目に関する数量に対してのサマリー
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
PROCEDURE CREATE_MEISAI_DAT( -- 入庫明細表情報作成
p$ST_YM IN DATE,
p$END_YM IN DATE,
p$PRNT_NO IN VARCHAR2,
p$RC OUT VARCHAR2
);
/********************************************************...
<Synopsis>
入庫明細表情報作成
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
PROCEDURE GENKA_ANOTHER_PRNT(
p$prnt_no IN COS_GT_GENKA.PRNT_NO%TYPE,
p$seq IN COS_GT_GENKA.SEQ%TYPE
);
/********************************************************...
<Synopsis>
その他出力用
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
FUNCTION CHK_HINGUN_CD(
I_TERM IN GM_HINGUNKS.TERM%TYPE,
I_C_CTG_CD IN GM_HINGUNKS.C_CTG_CD%TYPE
) RETURN NUMBER;
/********************************************************...
<Synopsis>
品群構成マスタチェック
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
NUMBER
*********************************************************...
END CPA_COS_ALLOC_PRNT_RE;
/
}}
終了行:
[[javasrc/sql]]
#highlighter(MySQL,number){{
CREATE OR REPLACE PACKAGE CPA_COS_ALLOC_PRNT_RE
/********************************************************...
<Synopsis>
原価帳票情報作成パッケージ
<Remarks>
指定した計算タイプ、年月などの原価計算結果から原価帳票...
下記原価帳票に対する情報を作成する。
原価計算表
入庫明細表
<History>
2017/10/05 K.Nonoshita 新規作成
2017/11/01 S.Yamamoto 単位の出力変更(費目行、小計と総...
*********************************************************...
IS
c$project_nm CONSTANT VARCHAR2(20) := '原価帳票情報作成';
c$project_cd CONSTANT VARCHAR2(100) := 'CPA_COS_ALLOC_P...
-- 帳票NO
g$PRINT_NO COS_GT_GENKA.PRNT_NO%TYPE;
g$SEQ COS_GT_GENKA.SEQ%TYPE := 0;
g$vCOS_GT_ATENKAI_UPD COS_GT_GENKA%ROWTYPE; ...
g$MNG_DPT_CD COS_GT_GENKA.MNG_DPT_CD%TYPE := NU...
TYPE tCOS_GW_GENKA_HIS IS TABLE OF COS_GW_GENKA_HIS%ROWT...
TYPE tCOS_GT_GENKA IS TABLE OF COS_GT_GENKA%ROWTYPE;
g$vCOS_GT_GENKA_ITM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ITM_TMP COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ITM_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_EXPNS COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_EXPNS_GRP COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_PP_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_PROD_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_DIRECT_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_INDIRECT_SUM COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ALEVEL0_OPT COS_GT_GENKA%ROWTYPE; ...
g$vCOS_GT_GENKA_ALVL0_OPT_SUM COS_GT_GENKA%ROWTYPE; ...
-- @2017/11/13 S.Nonoshita MOD START
p$UNIT_NM CM_UNIT.UNIT_NM%TYPE := NULL;
-- @2017/11/13 S.Nonoshita MOD END
-- 原価計算表用情報
CURSOR curCTG_GENKA(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M4.MST_RMRKS AS PAR_CTG_NM
,M2.P_CTG_CD AS CHD_CTG_CD
,M5.CTG_NM AS CHD_CTG_NM
,T3.EXPNS_CD
,M3.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M8.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS VAR_NUM
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(M3.VAR_FLG, 1,...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS FIX_NUM
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M4.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_C...
LEFT OUTER JOIN CM_ITEM_CTG M11 ON (M7.CTG_D = M11.ITEM...
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UN...
-- @2017/11/01 S.Yamamoto DEL END
LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND M1...
LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AND ...
LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND M1...
LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO, ...
LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10.C...
INNER JOIN COS_GW_ATENKAI W1 ON (W1.CALC_NO = T2.CALC...
WHERE 0 = 0
AND T1.REC_TYP = '0'
AND T3.EXPNS_CD IS NOT NULL
AND M1.P_CTG_CD = p$P_CTG_CD
AND M2.P_CTG_CD = p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, M3.PRINT_SORT ASC, M3.VAR_FLG;
-- 原価計算表用情報(品郡単位)
CURSOR curCTG_GENKA_ITM(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M4.MST_RMRKS AS PAR_CTG_NM
,M2.P_CTG_CD AS CHD_CTG_CD
,M5.CTG_NM AS CHD_CTG_NM
,T3.EXPNS_CD
,M11.ITEM_CTG_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
,M8.UNIT_NM AS UNIT_NM
-- @2017/11/06 S.Nonoshita MOD START
-- ,DECODE(M3.VAR_FLG, 1, T2.ACT_QTY, 0) ...
-- @2017/11/13 S.Nonoshita MOD START
-- ,DECODE(W2.CALC_NO, NULL, 0, T2.ACT_QTY...
,DECODE(W2.CALC_NO, NULL, 0, T2.ACT_QTY *...
-- @2017/11/13 S.Nonoshita MOD END
-- @2017/11/06 S.Nonoshita MOD END
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
-- @2017/11/06 S.Nonoshita MOD START
-- ,DECODE(M3.VAR_FLG, 1, 0, T2.ACT_QTY) AS FIX_NUM
,0 AS FIX_NUM
-- @2017/11/06 S.Nonoshita MOD END
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,M9.UNIT_CD
,NVL(M9.UNIT_WT,0) AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M4.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_C...
LEFT OUTER JOIN CM_ITEM_CTG M11 ON (M7.CTG_D = M11.ITEM...
LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UNIT...
-- @2017/11/13 S.Nonoshita MOD START
LEFT OUTER JOIN CM_ITM_UNIT M9 ON (M7.ITM_CD = M9.ITM_C...
-- @2017/11/13 S.Nonoshita MOD END
-- @2017/11/06 S.Nonoshita MOD START
-- LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND ...
-- LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AN...
-- LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND ...
-- LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO...
-- LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10...
LEFT OUTER JOIN COS_GW_ATENKAI W2 ON ( W2.CALC_NO = T1.C...
-- @2017/11/06 S.Nonoshita MOD END
WHERE 0 = 0
AND T1.REC_TYP = '0'
-- AND T2.LVL > 0
AND EXISTS ( SELECT * FROM COS_GW_ATENKAI W1 WHERE W1.CA...
AND M2.P_CTG_CD IS NOT NULL
AND M2.P_CTG_CD <> p$P_CTG_CD
AND M1.P_CTG_CD = p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY M7.CTG_D,T2.REC_GRP,T2.REC_NO ASC ;
vcurCTG_GENKA curCTG_GENKA%ROWTYPE;
vcurCTG_GENKA_ITM curCTG_GENKA_ITM%ROWTYPE;
-- <未入庫仕掛品>
CURSOR curNON_INPT_ITM(
p$REC_TYP IN VARCHAR2,
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M4.CTG_GRP_CD AS CTG_GRP_CD
,M4.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M3.MST_RMRKS AS PAR_CTG_NM
,NULL AS CHD_CTG_CD
,NULL AS CHD_CTG_NM
,T2.EXPNS_CD
,M2.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M6.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, ...
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, '3',T2.ACT_COS...
,CASE WHEN M2.VAR_FLG = 0 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS VAR_COST
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, '3',T1.ACT_...
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, '3',T2.ACT_...
,CASE WHEN M2.VAR_FLG = 1 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T1.REC_GRP
,0 AS REC_NO
,0 AS LVL
,M2.MST_RMRKS
,M2.PRINT_SORT
,M2.VAR_FLG
,M2.INDIRECT_FLG
,MAX(M2.VAR_FLG) OVER(PARTITION BY M2.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ASHUKEI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
INNER JOIN GM_HIMOKU M2 ON (T2.EXPNS_CD = M2.EXP...
LEFT OUTER JOIN GM_HINGUN M3 ON (M1.P_CTG_CD = M3.CTG...
LEFT OUTER JOIN GM_HINGUNG M4 ON (M3.CTG_GRP_CD = M4.C...
LEFT OUTER JOIN GM_HINMO M5 ON (T1.ITM_CD = M5.ITM_C...
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M6 ON (M5.UNIT_CD = M6.UN...
-- @2017/11/01 S.Yamamoto DEL END
INNER JOIN COS_GW_GENKA_HIS W1 ON (W1.CALC_NO = T1.CA...
WHERE 0 = 0
AND T1.REC_TYP = p$REC_TYP
AND M1.P_CTG_CD LIKE p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, T1.ITM_CD;
vcurNON_INPT_ITM curNON_INPT_ITM%ROWTYPE;
-- <中間体後工程払出>
CURSOR curRC_INPT(
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M6.CTG_GRP_CD AS CTG_GRP_CD
,M6.CTG_GRP_NM AS CTG_GRP_NM
,M2.P_CTG_CD AS PAR_CTG_CD
,M5.MST_RMRKS AS PAR_CTG_NM
,M1.P_CTG_CD AS CHD_CTG_CD
,M4.CTG_NM AS CHD_CTG_NM
,NULL AS EXPNS_CD
,M4.CTG_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M8.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS VAR_NUM
,DECODE(M3.VAR_FLG, 1, T3.ACT_COST, 0) AS VAR_AMT
,CASE WHEN M3.VAR_FLG = 0 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS VAR_COST
,CASE WHEN M9.CALC_TYP = '4' THEN DECODE(M3.VAR_FLG, 1,...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP = '1' THEN...
WHEN M9.CALC_TYP = '3' AND M10.TAB_TYP <> '1' THE...
ELSE 0
END AS FIX_NUM
,DECODE(M3.VAR_FLG, 1, 0, T3.ACT_COST) AS FIX_AMT
,CASE WHEN M3.VAR_FLG = 1 THEN 0
WHEN NVL(T2.ACT_QTY,0) = 0 THEN 0
ELSE (T3.ACT_COST/T2.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T2.REC_GRP
,T2.REC_NO
,T2.LVL
,M3.MST_RMRKS
,M3.PRINT_SORT
,M3.VAR_FLG
,M3.INDIRECT_FLG
,MAX(M3.VAR_FLG) OVER(PARTITION BY M3.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ATENKAI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN (select IT.CALC_NO, IT.REC_NO, IT.SML_RE...
,SUM(IT.ACT_COST) AS ACT_COST
,SUM(IT.STD_COST) AS STD_COST
,SUM(IT.DIFF_QT) AS DIFF_QT
,SUM(IT.DIFF_TM) AS DIFF_TM
,SUM(IT.DIFF_PR) AS DIFF_PR,
SUM(IT.DIFF_RT) AS DIFF_RT
FROM GT_AGENKA IT
GROUP BY IT.CALC_NO,IT.REC_NO,IT.SML_REC_NO, IT.EXPNS_CD
) T3 ON (T2.CALC_NO = T3.CALC_NO AND T2.REC_NO = T3.RE...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
LEFT OUTER JOIN GM_HINGUNKS M2 ON (T2.ITM_CD = M2.C_CTG...
INNER JOIN GM_HIMOKU M3 ON (T3.EXPNS_CD = M3.EXPNS_CD)
LEFT OUTER JOIN GM_HINGUN M4 ON (M1.P_CTG_CD = M4.CTG...
LEFT OUTER JOIN GM_HINGUN M5 ON (M2.P_CTG_CD = M5.CTG...
LEFT OUTER JOIN GM_HINGUNG M6 ON (M5.CTG_GRP_CD = M6.C...
LEFT OUTER JOIN CM_HINMO M7 ON (T2.ITM_CD = M7.ITM_CD)
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M8 ON (M7.UNIT_CD = M8.UN...
-- @2017/11/01 S.Yamamoto DEL END
LEFT OUTER JOIN GT_JPHINGUNR T4 ON (T1.YM = T4.YM AND M1...
LEFT OUTER JOIN GT_SYPHINGUNR1 T5 ON (T1.YM = T5.YM AND ...
LEFT OUTER JOIN GT_YPHINGUNR T6 ON (T1.YM = T6.YM AND M1...
LEFT OUTER JOIN GM_CALTYPE M9 ON (SUBSTR(T1.CALC_NO, ...
LEFT OUTER JOIN GM_CALTYPES M10 ON (M9.CAL_TYP = M10.C...
INNER JOIN COS_GW_ATENKAI W1 ON (W1.CALC_NO = T2.CALC...
WHERE 0 = 0
AND T1.REC_TYP = '0'
AND M2.P_CTG_CD LIKE 'RC%'
AND M2.P_CTG_CD = p$P_CTG_CD
AND M2.P_CTG_CD <> M1.P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
ORDER BY T1.MNG_DPT_CD, M3.PRINT_SORT ASC, M3.VAR_FLG;
vcurRC_INPT curRC_INPT%ROWTYPE;
-- <系内仕掛品>
CURSOR curALEVEL0_OPT(
p$VAR_FLG IN CHAR,
p$REC_TYP IN VARCHAR2,
p$P_CTG_CD IN VARCHAR2
)
IS
SELECT g$PRINT_NO AS PRNT_NO
,0 AS SEQ
,T1.MNG_DPT_CD
,M4.CTG_GRP_CD AS CTG_GRP_CD
,M4.CTG_GRP_NM AS CTG_GRP_NM
,M1.P_CTG_CD AS PAR_CTG_CD
,M3.MST_RMRKS AS PAR_CTG_NM
,NULL AS CHD_CTG_CD
,NULL AS CHD_CTG_NM
,T2.EXPNS_CD
,M2.EXPNS_NM AS PRNT_ITM_NM
,0 AS CTG1
,0 AS CTG2
,0 AS CTG3
-- @2017/11/01 S.Yamamoto MOD START
-- ,M6.UNIT_NM AS UNIT_NM
,NULL AS UNIT_NM
-- @2017/11/01 S.Yamamoto MOD END
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, ...
,DECODE(M2.VAR_FLG, 1, DECODE(p$REC_TYP, 'Q',T2.ACT_COS...
,CASE WHEN M2.VAR_FLG = 0 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS VAR_COST
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, 'Q',T1.ACT...
,DECODE(M2.VAR_FLG, 1, 0, DECODE(p$REC_TYP, 'Q',T2.ACT...
,CASE WHEN M2.VAR_FLG = 1 THEN 0
WHEN NVL(T1.ACT_QTY,0) = 0 THEN 0
ELSE (T2.ACT_COST/T1.ACT_QTY)
END AS FIX_COST
,0 AS TOTL_NUM
,0 AS TOTL_AMT
,0 AS TOTL_COST
,0 AS RATIO
,0 AS PROD_COST
,0 AS ORG_UPRI
,0 AS IN_QTY
,0 AS PROD_QTY
,0 AS SLS_AMT
,0 AS SLS_COST
,0 AS SEL_EXP
,0 AS ADM_EXP
,0 AS SLS_VAR_AMT
,0 AS VAR_TTL
,0 AS FIX_TTL
,T1.CALC_NO
,T1.REC_GRP
,0 AS REC_NO
,0 AS LVL
,M2.MST_RMRKS
,M2.PRINT_SORT
,M2.VAR_FLG
,M2.INDIRECT_FLG
,MAX(M2.VAR_FLG) OVER(PARTITION BY M2.MST_RMRKS) AS PP_...
,T1.ITM_CD
-- @2017/11/13 S.Nonoshita MOD START
,NULL AS UNIT_CD
,0 AS UNIT_WT
-- @2017/11/13 S.Nonoshita MOD END
,NULL AS AC_RSN_CD
,NULL AS AC_RSN_NM
FROM GT_ALEVEL0 T1
LEFT OUTER JOIN GT_ASHUKEI T2 ON (T1.CALC_NO = T2.CALC_...
LEFT OUTER JOIN GM_HINGUNKS M1 ON (T1.ITM_CD = M1.C_CTG...
INNER JOIN GM_HIMOKU M2 ON (T2.EXPNS_CD = M2.EXP...
LEFT OUTER JOIN GM_HINGUN M3 ON (M1.P_CTG_CD = M3.CTG...
LEFT OUTER JOIN GM_HINGUNG M4 ON (M3.CTG_GRP_CD = M4.C...
LEFT OUTER JOIN CM_HINMO M5 ON (T1.ITM_CD = M5.ITM_CD)
-- @2017/11/01 S.Yamamoto DEL START
-- LEFT OUTER JOIN CM_UNIT M6 ON (M5.UNIT_CD = M6.UN...
-- @2017/11/01 S.Yamamoto DEL END
INNER JOIN COS_GW_GENKA_HIS W1 ON (W1.CALC_NO = T1.CA...
WHERE 0 = 0
AND M2.VAR_FLG = p$VAR_FLG
AND T1.REC_TYP = p$REC_TYP
AND M1.P_CTG_CD LIKE p$P_CTG_CD
AND T1.MNG_DPT_CD = g$MNG_DPT_CD
;
vcurALEVEL0_OPT curALEVEL0_OPT%ROWTYPE;
PROCEDURE CREATE_PRNT_DAT(-- 帳票出力処理
p$RC OUT VARCHAR2,
p$PRNT_NO OUT VARCHAR2
) ;
/********************************************************...
<Synopsis>
帳票出力処理
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
FUNCTION IS_OPEN_TREE (
p$CALC_NO IN GT_ATENKAI.CALC_NO%TYPE,
p$REC_NO IN GT_ATENKAI.REC_NO%TYPE,
p$REC_GRP IN GT_ATENKAI.REC_GRP%TYPE,
p$LVL IN GT_ATENKAI.LVL%TYPE
) RETURN BOOLEAN ; -- 展開するかどうか
/********************************************************...
<Synopsis>
帳票出力処理
<Remarks>
<Arguments>
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXEC_GENKA_PRNT(
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
)
RETURN VARCHAR2; -- 原価帳票表情報作成
/********************************************************...
<Synopsis>
原価帳票表情報作成
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
VARCHAR2
*********************************************************...
FUNCTION IS_OUTPUT_EXPNS (
p$EXPNS_CD IN GM_HIMOKU.EXPNS_CD%TYPE
) RETURN BOOLEAN; -- 出力対象の費目かどうか
/********************************************************...
<Synopsis>
出力対象の費目かどうか
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION OPTION_GENKA_PRNT(
p$PRNT_SORT IN NUMBER,
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
)
RETURN VARCHAR2; -- 生産部門集計以降のデータ設定
/********************************************************...
<Synopsis>
生産部門集計以降のデータ設定
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
PROCEDURE OPTION_GENKA_VALUE(
p$GENKA_INDEX IN NUMBER,
p$P_CTG_CD IN GM_HINGUN.CTG_CD%TYPE
); -- 生産量 入庫量 売上金額 売上原価
/********************************************************...
<Synopsis>
生産量 入庫量 売上金額 売上原価
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXPNS_SUM(
d$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE,
s$curCTG_GENKA IN curCTG_GENKA%ROWTYPE
)
RETURN COS_GT_GENKA%ROWTYPE; -- 左から右の足し算を行う。
/********************************************************...
<Synopsis>
費目に関する数量に対してのサマリー
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
FUNCTION EXPNS_MINUS(
d$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE,
s$curCTG_GENKA IN COS_GT_GENKA%ROWTYPE
)
RETURN COS_GT_GENKA%ROWTYPE; -- 左から右の引き算を行う。
/********************************************************...
<Synopsis>
費目に関する数量に対してのサマリー
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
BOOLEAN
*********************************************************...
PROCEDURE CREATE_MEISAI_DAT( -- 入庫明細表情報作成
p$ST_YM IN DATE,
p$END_YM IN DATE,
p$PRNT_NO IN VARCHAR2,
p$RC OUT VARCHAR2
);
/********************************************************...
<Synopsis>
入庫明細表情報作成
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
PROCEDURE GENKA_ANOTHER_PRNT(
p$prnt_no IN COS_GT_GENKA.PRNT_NO%TYPE,
p$seq IN COS_GT_GENKA.SEQ%TYPE
);
/********************************************************...
<Synopsis>
その他出力用
<Remarks>
<Arguments>
<ReturnValue>
*********************************************************...
FUNCTION CHK_HINGUN_CD(
I_TERM IN GM_HINGUNKS.TERM%TYPE,
I_C_CTG_CD IN GM_HINGUNKS.C_CTG_CD%TYPE
) RETURN NUMBER;
/********************************************************...
<Synopsis>
品群構成マスタチェック
<Remarks>
<Arguments>
<Item> VARCHAR2
<ReturnValue>
NUMBER
*********************************************************...
END CPA_COS_ALLOC_PRNT_RE;
/
}}
ページ名: