EBS:采购-审批分配

 

-- 审批分配（N:PO>>设置>>审批>>审批分配）

SELECT HOU.SHORT_CODE ||'-'|| HOU.NAME AS OU_NAME, -- 业务实体
PPOS.POSITION_ID ,
PPOS.NAME AS POSITION_NAME, -- 职位名称
PJ.JOB_ID,
PJ.NAME JOB_NAME , -- 任务名称（职务）
PPC.CONTROL_FUNCTION_ID AS 单据类型ID,
POCF.CONTROL_FUNCTION_NAME AS 单据类型,
PCG.CONTROL_GROUP_ID,
PCG.CONTROL_GROUP_NAME , -- 审批组名称
PCG.DESCRIPTION AS CONTROL_GROUP_DESC, -- 审批组说明
PCR.CONTROL_RULE_ID,
PCR.AMOUNT_LIMIT -- 限额
-- PPC.*
FROM APPS.HR_OPERATING_UNITS HOU,
APPS.PER_POSITIONS_V PPOS, -- 职位表
APPS.PER_JOBS_VL PJ, -- 职务表
APPS.PO_POSITION_CONTROLS PPC, -- 审批分配
PO.po_control_functions pocf , -- 单据类型
APPS.PO_CONTROL_GROUPS PCG, -- 审批组
APPS.PO_CONTROL_RULES PCR -- 审批规则
WHERE 1=1 --PPC.ORG_ID = 242
-- AND PPC.CONTROL_GROUP_ID IN (163,164, 177 )
-- AND PPC.JOB_ID IN ( 70, 76, 78,80 )
AND PPC.JOB_ID = PJ.JOB_ID
AND PPC.POSITION_ID = PPOS.POSITION_ID
AND PPC.CONTROL_GROUP_ID = PCG.CONTROL_GROUP_ID
AND PPC.CONTROL_FUNCTION_ID = POCF.CONTROL_FUNCTION_ID
AND PPC.ORG_ID = HOU.ORGANIZATION_ID
-- AND HOU.SHORT_CODE = '330'
AND PCG.CONTROL_GROUP_ID = PCR.CONTROL_GROUP_ID
AND PCR.RULE_TYPE_CODE = 'INCLUDE' -- 类型
AND PCR.OBJECT_CODE = 'DOCUMENT_TOTAL' -- DOCUMENT_TOTAL:单据总额， ACCOUNT_RANGE:账户范围
AND PPOS.POSITION_ID = 28182 -- 14181
ORDER BY HOU.SHORT_CODE, PPOS.NAME, PPC.CONTROL_GROUP_ID