QA: Senfor Intl - Quality Collection Plan Details Extract

-- Senfor Quality Collection Plan Details Extract
 
-- No actions
select distinct 
mp.organization_code ORG,
qpv.plan_id,
qpv.name,
qpv.description,
qpv.plan_type_meaning,
qpcv.char_id,
qpcv.char_name,
qpcv.char_type_meaning,
qpcv.prompt_sequence,
qpcv.prompt,
qpcv.enabled_meaning,
qpcv.mandatory_meaning,
qpcv.default_value,
qpcv.values_exist_meaning,
qpcv.displayed_meaning,
qpcv.read_only_meaning,
qpcv.ss_poplist_meaning,
qpcv.datatype_meaning,
qpcv.result_column_name,
qpcv.display_length,
nvl(qpcv.decimal_precision,0)decimal_precision,
nvl(qpcv.data_entry_hint,' ') data_entry_hint,
0 trigger_sequence,
0 plan_char_action_trigger_id,
' ' operator_meaning,
0 plan_char_action_id,
' ' assigned_char_name,
' ' action_description,
' ' message ,
' ' token_name ,
' 'char_name 
from 
QA_PLANS_V qpv,
QA_PLAN_CHARS_V qpcv,
MTL_PARAMETERS mp
where 1=1
--and qpcv.organization_id = (select mp.organization_id from mtl_parameters mp where mp.organization_code = '&Org')  --207  --1071
--and qpv.name like '405 ASSEMBLY COLLECTION PLAN'  --'WIP COMPLETION COLLECTION PLAN'
and qpcv.plan_id = qpv.plan_id
and qpv.organization_id=mp.organization_id
and qpcv.enabled_flag = 1
 
UNION
 
--With actions
select 
mp.organization_code ORG,
qpv.plan_id,
qpv.name,
qpv.description,
qpv.plan_type_meaning,
qpcv.char_id,
qpcv.char_name,
qpcv.char_type_meaning,
qpcv.prompt_sequence,
qpcv.prompt,
qpcv.enabled_meaning,
qpcv.mandatory_meaning,
qpcv.default_value,
qpcv.values_exist_meaning,
qpcv.displayed_meaning,
qpcv.read_only_meaning,
qpcv.ss_poplist_meaning,
qpcv.datatype_meaning,
qpcv.result_column_name,
qpcv.display_length,
nvl(qpcv.decimal_precision,0)decimal_precision,
nvl(qpcv.data_entry_hint,' ') data_entry_hint,
t.trigger_sequence,
t.plan_char_action_trigger_id,
t.operator_meaning,
t.plan_char_action_id,
t.assigned_char_name,
t.action_description,
t.message "Action",
t.token_name,
t.char_name 
from 
QA_PLANS_V qpv,
QA_PLAN_CHARS_V qpcv, 
MTL_PARAMETERS mp,
(select distinct ' ',
    qpcatv.plan_id,
    qpcatv.char_id,
    qpcatv.trigger_sequence,
    qpcatv.plan_char_action_trigger_id,
    qpcatv.operator_meaning ,
    a.plan_char_action_id,
    a.assigned_char_name,
    a.action_description,
    a.message,
    a.token_name  ,
    a.char_name 
 from
    QA_PLAN_CHAR_ACTION_TRIGGERS_V qpcatv ,
 
    (select distinct ' ',
         qpcav.plan_id,
         qpcav.char_id,
         qpcav.plan_char_action_trigger_id,
         qpcav.plan_char_action_id,
         qpcav.trigger_sequence,
         qpcav.assigned_char_name,
         qpcav.action_description,
         qpcav.message,
         qpcaov.token_name token_name ,
         qpcaov.char_name char_name
     from
        QA_PLAN_CHAR_ACTIONS_V qpcav,
        QA_PLAN_CHAR_ACTION_OUTPUTS_V qpcaov
     where
        qpcaov.plan_char_action_id = qpcav.plan_char_action_id
        and qpcaov.action_id = qpcav.action_id
        and qpcaov.plan_char_action_trigger_id = qpcav.plan_char_action_trigger_id
        and qpcav.plan_char_action_trigger_id >0
 
        UNION
 
        select distinct
        ' ',
         qpcav.plan_id,
         qpcav.char_id,
         qpcav.plan_char_action_trigger_id,
         qpcav.plan_char_action_id,
         qpcav.trigger_sequence,
         qpcav.assigned_char_name,
         qpcav.action_description,
         qpcav.message,
         ' ' token_name ,
         ' ' char_name
     from
        QA_PLAN_CHAR_ACTIONS_V qpcav
     where
        qpcav.plan_char_action_trigger_id >0
        )a
 where
    qpcatv.plan_id =a.plan_id
    and qpcatv.char_id = a.char_id
    and a.trigger_sequence = qpcatv.trigger_sequence
    and a.plan_char_action_trigger_id = qpcatv.plan_char_action_trigger_id
    and qpcatv.plan_char_action_trigger_id > 0
    )t
 
where 1=1
--and qpcv.organization_id = (select mp.organization_id from mtl_parameters mp where mp.organization_code = '&Org') --207 --1071
--and qpv.name like '405 ASSEMBLY COLLECTION PLAN'  --'WIP COMPLETION COLLECTION PLAN'
and qpcv.plan_id = qpv.plan_id
and qpv.organization_id=mp.organization_id
and t.plan_id = qpcv.plan_id
and t.char_id = qpcv.char_id
and qpcv.enabled_flag = 1
 
order by 3,8