EBS:税务管理-应用产品税选项

-- N: 税务管理员>>默认值和控制>>应用产品税选项
-- 应用产品税选项
SELECT HOU.SHORT_CODE ||'-'|| HOU.NAME AS ORG_NAME ,
       FA.APPLICATION_SHORT_NAME||'-'||FA.APPLICATION_NAME AS "应用程序名称",
       DEF_OPTION_HIER_1_CODE AS "默认顺序1",
       CASE ZPO.APPLICATION_ID WHEN 201 THEN 
         (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_PO_DEFAULT_HIERARCHY' --'ZX_TAX_METHOD_OPTIONS'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_1_CODE)
          WHEN 200 THEN 
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_AP_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_1_CODE)
          WHEN 8407 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_IGC_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_1_CODE)
          WHEN 222 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_AR_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_1_CODE)
          WHEN 275 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_PA_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_1_CODE
          ) ELSE  ZPO.DEF_OPTION_HIER_1_CODE
          END AS DEF_OPTION_HIER_1_NAME,
         DEF_OPTION_HIER_2_CODE AS "默认顺序2",
         CASE ZPO.APPLICATION_ID WHEN 201 THEN 
         (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_PO_DEFAULT_HIERARCHY' --'ZX_TAX_METHOD_OPTIONS'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_2_CODE)
          WHEN 200 THEN 
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_AP_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_2_CODE)
          WHEN 8407 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_IGC_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_2_CODE)
          WHEN 222 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_AR_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_2_CODE)
          WHEN 275 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_PA_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_2_CODE
          ) ELSE  ZPO.DEF_OPTION_HIER_2_CODE
          END AS DEF_OPTION_HIER_2_NAME,  
         DEF_OPTION_HIER_3_CODE AS "默认顺序3",
         CASE ZPO.APPLICATION_ID WHEN 201 THEN 
         (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_PO_DEFAULT_HIERARCHY' --'ZX_TAX_METHOD_OPTIONS'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_3_CODE)
          WHEN 200 THEN 
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_AP_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_3_CODE)
          WHEN 8407 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_IGC_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_3_CODE)
          WHEN 222 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_AR_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_3_CODE)
          WHEN 275 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_PA_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_3_CODE
          ) ELSE  ZPO.DEF_OPTION_HIER_3_CODE
          END AS DEF_OPTION_HIER_3_NAME,
         DEF_OPTION_HIER_4_CODE AS "默认顺序4",
         CASE ZPO.APPLICATION_ID WHEN 201 THEN 
         (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_PO_DEFAULT_HIERARCHY' --'ZX_TAX_METHOD_OPTIONS'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_4_CODE)
          WHEN 200 THEN 
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_AP_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_4_CODE)
          WHEN 8407 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE = 'ZX_IGC_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_4_CODE)
          WHEN 222 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_AR_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_4_CODE)
          WHEN 275 THEN
          (SELECT MEANING FROM APPS.FND_LOOKUP_VALUES_VL  FLV  
          WHERE  FLV.LOOKUP_TYPE ='ZX_PA_DEFAULT_HIERARCHY'
          AND FLV.LOOKUP_CODE = ZPO.DEF_OPTION_HIER_4_CODE
          ) ELSE  ZPO.DEF_OPTION_HIER_4_CODE
          END AS DEF_OPTION_HIER_4_NAME,
         TAX_CLASSIFICATION_CODE AS "财务选项税分类"
        ,ZPO.*
  FROM ZX.ZX_PRODUCT_OPTIONS_ALL ZPO,
       APPS.HR_OPERATING_UNITS HOU,
       APPS.FND_APPLICATION_VL FA
 where 1=1 -- AND ZPO.LAST_UPDATE_DATE > SYSDATE - 1 
  AND ZPO.ORG_ID = HOU.organization_id
  AND ZPO.APPLICATION_ID = FA.APPLICATION_ID 
ORDER BY ZPO.ORG_ID , ZPO.APPLICATION_ID ;