P_SU_AJBG_DP0101
DP0101Job.py
获取消耗信息 按照类型调用不同存储过程
SU_AJBG_DP0102 主原料
BPC 能耗
BPC 锌耗
从main函数传入参数
p_account,p_cost_center,p_unit,p_account_period_start,p_account_period_end,p_data_type

子步骤0

删库
DELETE FROM BGRAGGCB.SU_AJBG_DP0101
WHERE LEFT(WORK_TIME,14) >= '20210225220000'
AND LEFT(WORK_TIME,14) < '20210226220000'
AND ACCOUNT = '1001'
AND COST_CENTER = 'MIDF' AND UNIT = 'Q161'
AND DATA_TYPE = '0'
其中
20210225220000
20210226220000
1001
MIDF
Q161
0
是传入的参数p_account_period_start,p_account_period_end,p_account,p_cost_center,p_unit,p_data_type

获取主原料的

子步骤1

dbprod7
SELECT REC_ID,
ACCOUNT,
FACTORY,
UNIT,
TEAM,
SHIFT,
COST_CENTER,
WORK_TIME,
PROCESS_START_TIME,
PROCESS_END_TIME,
PRODUCT_CODE,
ST_NO,
IN_PRODUCT_CODE,
MAT_NO ,
IN_MAT_NO,
APP_THROW_AI_MODE,
DESIGN_ANNEAL_DIAGRAM_CODE,
TRIM_FLAG,
MAT_ACT_WIDTH,
MAT_ACT_THICK,
IN_MAT_WIDTH,
IN_MAT_THICK,
PLAN_NO,
TRIM_WIDTH,
IN_MAT_INNER_DIA,
CUST_ORDER_NO,
PICKL_TRIM_FLAG,
SORT_GRADE_CODE,
TOP_PLATE_WT,
BOT_PLATE_WT,
LAYER_TYPE,
MAT_ACT_LEN,
MAT_ACT_AREA,
TOP_COAT_WT,
BOT_COAT_WT,
COALESCE(WT,0) AS WT,
ACT_WT,
IN_WT,
ACT_IN_WT,
CASE WHEN APP_THROW_AI_MODE NOT LIKE '%PN%' THEN COALESCE(ACT_WT,0) ELSE COALESCE(ACT_IN_WT,0) END AS ACT_N ,
IN_PRODUCT_CODE AS CONSUME_ITEM,
'主原料' AS CONSUME_DESC,
'T' AS CONSUME_UNIT,
COALESCE(ACT_IN_WT,0) AS CONSUME_N,
'0' AS DATA_TYPE,
LAS_NOTCH_FLAG
FROM BGRAGGCB.SU_AJBG_DP0102
WHERE ACCOUNT = '1001'
AND LEFT(WORK_TIME,14) >= '20210225220000'
AND LEFT(WORK_TIME,14) < '20210226220000'
AND COST_CENTER = 'MIDF' AND UNIT='Q161'
AND DATA_TYPE = '0'
其中
0
1001
20210225220000
20210226220000
MIDF
Q161
0
是传入的参数p_data_type,p_account,p_account_period_start,p_account_period_end,p_cost_center,p_unit,p_data_type
得到df1

子步骤2

dbprod7
SELECT
WCE,DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE
FROM BGRAGGCB.TACACTY
WHERE ACCOUNT = '1001'
1001是传入的参数p_account
得到df2
将df1 merge df2 left
on IN_PRODUCT_CODE
得到df3
df3是拼接该机组所对应的主原料科目的dp0102数据

子步骤2.1

将df3插入到数据库
BGRAGGCB.SU_AJBG_DP0101 中

子步骤3

dbprod7
读取该机组其他的成本科目
SELECT DISTINCT WCE FROM BGRAGGCB.WH_CF00_XX0660
WHERE ACCOUNT= '1001' AND COST_CENTER='MIDF' AND LEFT(WCE,2)<>'15'
AND WCE NOT IN ('59086','59087','59088')
ORDER BY WCE
其中1001，MIDF是传入的参数，p_account，p_cost_center
得到df4
对df4的每一个WCE（成本科目编号）进行循环查询,假设第一个是68300
v_wce='68300'
dbprod7
读取该机组所有维护过逻辑的成本科目，判断WCE有前几位与维护表的相同
5位都相同的个数IS_WCE5
SELECT COUNT(1) as IS_WCE5
FROM BGRAGGCB.WH_AJBG_DP0110
WHERE ACCOUNT='1001' AND COST_CENTER='MIDF' AND UNIT='Q161' AND SOURCE_NO='0'
AND WCE=LEFT('68300',5)||''
其中1001，MIDF，Q161,0是传入参数p_account,p_cost_center,p_unit,p_data_type
68300是df4循环的传入参数v_wce

前四位相同的个数IS_WCE4
SELECT COUNT(1) as IS_WCE4
FROM BGRAGGCB.WH_AJBG_DP0110
WHERE ACCOUNT='1001' AND COST_CENTER='MIDF' AND UNIT='Q161' AND SOURCE_NO='0'
AND WCE= LEFT('68300',4)||'@＇
其中1001，MIDF，Q161,0是传入参数p_account,p_cost_center,p_unit,p_data_type
68300是df4循环的传入参数v_wce

前四位相同的个数IS_WCE3
SELECT COUNT(1) as IS_WCE3
FROM BGRAGGCB.WH_AJBG_DP0110
WHERE ACCOUNT='1001' AND COST_CENTER='MIDF' AND UNIT='Q161' AND SOURCE_NO='0'
AND WCE= LEFT('68300',3)||'@@'
其中1001，MIDF，Q161,0是传入参数p_account,p_cost_center,p_unit,p_data_type
68300是df4循环的传入参数v_wce

前四位相同的个数IS_WCE2
SELECT COUNT(1) as IS_WCE2
FROM BGRAGGCB.WH_AJBG_DP0110
WHERE ACCOUNT='1001' AND COST_CENTER='MIDF' AND UNIT='Q161' AND SOURCE_NO='0'
AND WCE= LEFT('68300',2)||'@@@'
其中1001，MIDF，Q161,0是传入参数p_account,p_cost_center,p_unit,p_data_type
68300是df4循环的传入参数v_wce

前四位相同的个数IS_WCE1
SELECT COUNT(1) as IS_WCE1
FROM BGRAGGCB.WH_AJBG_DP0110
WHERE ACCOUNT='1001' AND COST_CENTER='MIDF' AND UNIT='Q161' AND SOURCE_NO='0'
AND WCE= LEFT('68300',1)||'@@@@'
其中1001，MIDF，Q161,0是传入参数p_account,p_cost_center,p_unit,p_data_type
68300是df4循环的传入参数v_wce

依次判断IS_WCE5,IS_WCE4,IS_WCE3,IS_WCE2,IS_WCE1是否为0

IF IS_WCE5<>0
用参数p_account,
p_cost_center_org = p_cost_center,
p_cost_center, p_unit, p_account_period_start, p_account_period_end,
p_wce_org =v_wce,p_wce=LEFT('v_wce',5)||'',
p_data_type
去调用DP0110Job

IF IS_WCE5=0, IS_WCE4<>0
用参数p_account,
p_cost_center_org = p_cost_center,
p_cost_center, p_unit, p_account_period_start, p_account_period_end,
p_wce_org =v_wce,p_wce=LEFT(v_wce,4)||'@',
p_data_type
去调用DP0110Job

IF IS_WCE5=0, IS_WCE4=0, IS_WCE3<>0
用参数p_account,
p_cost_center_org = p_cost_center,
p_cost_center, p_unit, p_account_period_start, p_account_period_end,
p_wce_org =v_wce,p_wce=LEFT(v_wce,3)||'@@',
p_data_type
去调用DP0110Job

IF IS_WCE5=0, IS_WCE4=0, IS_WCE3=0, IS_WCE2<>0
用参数p_account,
p_cost_center_org = p_cost_center,
p_cost_center, p_unit, p_account_period_start, p_account_period_end,
p_wce_org =v_wce,p_wce=LEFT(v_wce,2)||'@@@',
p_data_type
去调用DP0110Job

IF IS_WCE5=0, IS_WCE4=0, IS_WCE3=0, IS_WCE2=0, IS_WCE1<>0
用参数p_account,
p_cost_center_org = p_cost_center,
p_cost_center, p_unit, p_account_period_start, p_account_period_end,
p_wce_org =v_wce,p_wce=LEFT(v_wce,1)||'@@@@',
p_data_type
去调用DP0110Job

备注
data type是 0 D M
分别是 实时、每日、每月