EBS: PO单据类型查询

-- PO>>设置>>采购>>单据类型 ; 
SELECT pdt.document_type_code,
         pdt.org_id,
         pdt.document_subtype,
         pdt.document_template_code,
         pdt.contract_template_code,
         xtv1.template_name AS document_types_layout,
         xtv2.template_name AS contract_terms_layout,
         plc1.displayed_field || ' ' || plc2.displayed_field AS  "类型",--TYPE,
         decode(pdt.document_type_code,
               'RFQ',
               decode(pdt.document_subtype,
                      'STANDARD',
                      'DeleteDisabled',
                      'CATALOG',
                      'DeleteDisabled',
                      'BID',
                      'DeleteDisabled',
                      'DeleteEnabled'),
               'QUOTATION',
               decode(pdt.document_subtype,
                      'STANDARD',
                      'DeleteDisabled',
                      'CATALOG',
                      'DeleteDisabled',
                      'BID',
                      'DeleteDisabled',
                      'DeleteEnabled'),
               'DeleteDisabled') AS delete_switcher,
        pdt.type_name,
      --  pdt.document_type_code, 
      -- pdt.document_subtype,
       pdt.type_name as "单据名",
       pdt.ARCHIVE_EXTERNAL_REVISION_CODE,
       pdt.CAN_PREPARER_APPROVE_FLAG AS "责任人可以审批",
       pdt.CAN_APPROVER_MODIFY_DOC_FLAG AS "责任人可以修改",
       DECODE(pdt.FORWARDING_MODE_CODE, 'HIERARCHY','层次结构','DIRECT','直接',pdt.FORWARDING_MODE_CODE)  AS "转发方式",
       pdt.CAN_CHANGE_FORWARD_FROM_FLAG AS "可以更改转发人" ,
       pdt.CAN_CHANGE_FORWARD_TO_FLAG AS "可以更改转发接收人",
       pdt.CAN_CHANGE_APPROVAL_PATH_FLAG AS "可以更改审批层次结构" ,
       pdt.QUOTATION_CLASS_CODE,
       pdt.DEFAULT_APPROVAL_PATH_ID AS "默认层次结构ID",
       (SELECT NAME FROM  PER_POSITION_STRUCTURES_V  PPS 
        WHERE PPS.position_structure_id =  pdt.DEFAULT_APPROVAL_PATH_ID
         AND ROWNUM= 1 ) AS "默认层次结构",
       pdt.SECURITY_LEVEL_CODE AS "安全级别",
       pdt.ACCESS_LEVEL_CODE AS "访问级别",
       pdt.DISABLED_FLAG,
       DECODE(pdt.WF_APPROVAL_ITEMTYPE,'REQAPPRV','申请',
        'POREQCHA','申请人更改订单审批',
        'PORPOCHA','申请人的PO更改审批',
        pdt.WF_APPROVAL_ITEMTYPE
        ) AS "审批工作流",
       DECODE(pdt.WF_APPROVAL_PROCESS,'MAIN_REQAPPRV_PROCESS','主要申请审批',pdt.WF_APPROVAL_PROCESS) AS "工作流启动流程",
       DECODE(pdt.WF_CREATEDOC_ITEMTYPE,'CREATEPO','PO 创建单据',pdt.WF_CREATEDOC_ITEMTYPE) AS "自动创建工作流",
       DECODE(pdt.WF_CREATEDOC_PROCESS,'OVERALL_AUTOCREATE_PROCESS','总体单据创建/启动审批',
       pdt.WF_CREATEDOC_PROCESS
       ) AS "自创工作流启动流程", --"自动创建工作流启动流程",
       pdt.AME_TRANSACTION_TYPE AS "审批事务处理类型",
       pdt.USE_CONTRACT_FOR_SOURCING_FLAG,
       pdt.INCLUDE_NONCATALOG_FLAG,
       pdt.DOCUMENT_TEMPLATE_CODE,
       pdt.CONTRACT_TEMPLATE_CODE,
      ( SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE ORGANIZATION_ID =  pdt.org_id ) AS "组织",
       pdt.last_update_date 
   FROM po_document_types_all_vl pdt,
        xdo_templates_vl         xtv1,
        xdo_templates_vl         xtv2,
        po_lookup_codes          plc1,
        po_lookup_codes          plc2
  WHERE xtv1.template_code(+) = pdt.document_template_code
    AND xtv2.template_code(+) = pdt.contract_template_code
    AND pdt.document_type_code = plc1.lookup_code
    AND plc1.lookup_type = 'DOCUMENT TYPE'
    AND pdt.document_subtype = plc2.lookup_code
    AND plc2.lookup_type = decode(pdt.document_type_code,
                                  'REQUISITION',
                                  'REQUISITION TYPE',
                                  'RFQ',
                                  'RFQ SUBTYPE',
                                  'QUOTATION',
                                  'QUOTATION SUBTYPE',
                                  'DOCUMENT SUBTYPE')
   -- AND pdt.org_id =281
 ORDER BY   pdt.org_id , pdt.last_update_date