ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 52162B9B8C221F1DE0630100007FADC0 QA Collection Plan Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US B1C2D3E4F5A6B7C8D9E0F1A2B3C4D5E6Y select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, to_char(null) element_name, to_number(null) prompt_sequence, to_char(null) element_prompt, to_char(null) element_enabled, to_char(null) element_mandatory, to_char(null) default_value, to_char(null) displayed, to_char(null) read_only, to_char(null) ss_poplist, to_char(null) information, to_number(null) decimal_precision, to_char(null) uom_code, to_char(null) value_code, to_char(null) value_description, to_number(null) action_trigger_seq, to_char(null) action_operator, to_char(null) action_low_value, to_char(null) action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 1 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, qptv.transaction_description, xxen_util.meaning(decode(qpt.mandatory_collection_flag,1,'Y','N'),'YES_NO',0) mandatory_collection, xxen_util.meaning(decode(qpt.background_collection_flag,1,'Y','N'),'YES_NO',0) background_collection, xxen_util.meaning(decode(qpt.enabled_flag,1,'Y','N'),'YES_NO',0) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, to_char(null) element_name, to_number(null) prompt_sequence, to_char(null) element_prompt, to_char(null) element_enabled, to_char(null) element_mandatory, to_char(null) default_value, to_char(null) displayed, to_char(null) read_only, to_char(null) ss_poplist, to_char(null) information, to_number(null) decimal_precision, to_char(null) uom_code, to_char(null) value_code, to_char(null) value_description, to_number(null) action_trigger_seq, to_char(null) action_operator, to_char(null) action_low_value, to_char(null) action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 2 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_transactions qpt, qa_plan_transactions_v qptv where qp.organization_id=mp.organization_id and qp.plan_id=qpt.plan_id and qpt.plan_transaction_id=qptv.plan_transaction_id and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, qptv.transaction_description, xxen_util.meaning(decode(qpt.mandatory_collection_flag,1,'Y','N'),'YES_NO',0) mandatory_collection, xxen_util.meaning(decode(qpt.background_collection_flag,1,'Y','N'),'YES_NO',0) background_collection, xxen_util.meaning(decode(qpt.enabled_flag,1,'Y','N'),'YES_NO',0) transaction_enabled, qctdv.collection_trigger_description collection_trigger, xxen_util.meaning(qpct.operator,'QA_OPERATOR',700) trigger_operator, qpct.low_value trigger_low_value, qpct.high_value trigger_high_value, to_char(null) element_name, to_number(null) prompt_sequence, to_char(null) element_prompt, to_char(null) element_enabled, to_char(null) element_mandatory, to_char(null) default_value, to_char(null) displayed, to_char(null) read_only, to_char(null) ss_poplist, to_char(null) information, to_number(null) decimal_precision, to_char(null) uom_code, to_char(null) value_code, to_char(null) value_description, to_number(null) action_trigger_seq, to_char(null) action_operator, to_char(null) action_low_value, to_char(null) action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 3 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_transactions qpt, qa_plan_transactions_v qptv, qa_plan_collection_triggers qpct, qa_txn_collection_triggers_v qctdv where qp.organization_id=mp.organization_id and qp.plan_id=qpt.plan_id and qpt.plan_transaction_id=qptv.plan_transaction_id and qpt.plan_transaction_id=qpct.plan_transaction_id and qpct.collection_trigger_id=qctdv.collection_trigger_id and qpt.transaction_number=qctdv.transaction_number and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, qc.name element_name, qpc.prompt_sequence, qpc.prompt element_prompt, xxen_util.meaning(decode(qpc.enabled_flag,1,'Y','N'),'YES_NO',0) element_enabled, xxen_util.meaning(decode(qpc.mandatory_flag,1,'Y','N'),'YES_NO',0) element_mandatory, qpc.default_value, xxen_util.meaning(decode(qpc.displayed_flag,1,'Y','N'),'YES_NO',0) displayed, xxen_util.meaning(decode(qpc.read_only_flag,1,'Y','N'),'YES_NO',0) read_only, xxen_util.meaning(decode(qpc.ss_poplist_flag,1,'Y','N'),'YES_NO',0) ss_poplist, xxen_util.meaning(decode(qpc.information_flag,1,'Y','N'),'YES_NO',0) information, qpc.decimal_precision, qpc.uom_code, to_char(null) value_code, to_char(null) value_description, to_number(null) action_trigger_seq, to_char(null) action_operator, to_char(null) action_low_value, to_char(null) action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 4 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc where qp.organization_id=mp.organization_id and qpc.plan_id=qp.plan_id and qpc.char_id=qc.char_id and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, qc.name element_name, qpc.prompt_sequence, qpc.prompt element_prompt, xxen_util.meaning(decode(qpc.enabled_flag,1,'Y','N'),'YES_NO',0) element_enabled, xxen_util.meaning(decode(qpc.mandatory_flag,1,'Y','N'),'YES_NO',0) element_mandatory, qpc.default_value, xxen_util.meaning(decode(qpc.displayed_flag,1,'Y','N'),'YES_NO',0) displayed, xxen_util.meaning(decode(qpc.read_only_flag,1,'Y','N'),'YES_NO',0) read_only, xxen_util.meaning(decode(qpc.ss_poplist_flag,1,'Y','N'),'YES_NO',0) ss_poplist, xxen_util.meaning(decode(qpc.information_flag,1,'Y','N'),'YES_NO',0) information, qpc.decimal_precision, qpc.uom_code, qpcvl.short_code value_code, qpcvl.description value_description, to_number(null) action_trigger_seq, to_char(null) action_operator, to_char(null) action_low_value, to_char(null) action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 5 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc, qa_plan_char_value_lookups qpcvl where qp.organization_id=mp.organization_id and qpc.plan_id=qp.plan_id and qpc.char_id=qc.char_id and qpcvl.plan_id=qpc.plan_id and qpcvl.char_id=qpc.char_id and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, qc.name element_name, qpc.prompt_sequence, qpc.prompt element_prompt, xxen_util.meaning(decode(qpc.enabled_flag,1,'Y','N'),'YES_NO',0) element_enabled, xxen_util.meaning(decode(qpc.mandatory_flag,1,'Y','N'),'YES_NO',0) element_mandatory, qpc.default_value, xxen_util.meaning(decode(qpc.displayed_flag,1,'Y','N'),'YES_NO',0) displayed, xxen_util.meaning(decode(qpc.read_only_flag,1,'Y','N'),'YES_NO',0) read_only, xxen_util.meaning(decode(qpc.ss_poplist_flag,1,'Y','N'),'YES_NO',0) ss_poplist, xxen_util.meaning(decode(qpc.information_flag,1,'Y','N'),'YES_NO',0) information, qpc.decimal_precision, qpc.uom_code, to_char(null) value_code, to_char(null) value_description, qpcat.trigger_sequence action_trigger_seq, xxen_util.meaning(qpcat.operator,'QA_OPERATOR',700) action_operator, qpcat.low_value_other action_low_value, qpcat.high_value_other action_high_value, to_char(null) action_name, to_char(null) assign_type, to_char(null) assigned_element, to_char(null) alert_action_set, to_char(null) alert_action, to_char(null) status_code, to_char(null) action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 6 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc, qa_plan_char_action_triggers qpcat where qp.organization_id=mp.organization_id and qpc.plan_id=qp.plan_id and qpc.char_id=qc.char_id and qpcat.plan_id=qpc.plan_id and qpcat.char_id=qpc.char_id and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, qc.name element_name, qpc.prompt_sequence, qpc.prompt element_prompt, xxen_util.meaning(decode(qpc.enabled_flag,1,'Y','N'),'YES_NO',0) element_enabled, xxen_util.meaning(decode(qpc.mandatory_flag,1,'Y','N'),'YES_NO',0) element_mandatory, qpc.default_value, xxen_util.meaning(decode(qpc.displayed_flag,1,'Y','N'),'YES_NO',0) displayed, xxen_util.meaning(decode(qpc.read_only_flag,1,'Y','N'),'YES_NO',0) read_only, xxen_util.meaning(decode(qpc.ss_poplist_flag,1,'Y','N'),'YES_NO',0) ss_poplist, xxen_util.meaning(decode(qpc.information_flag,1,'Y','N'),'YES_NO',0) information, qpc.decimal_precision, qpc.uom_code, to_char(null) value_code, to_char(null) value_description, qpcat.trigger_sequence action_trigger_seq, xxen_util.meaning(qpcat.operator,'QA_OPERATOR',700) action_operator, qpcat.low_value_other action_low_value, qpcat.high_value_other action_high_value, qa.description action_name, decode(qpca.assign_type,'F','Formula','S','SQL Statement') assign_type, qc_assigned.name assigned_element, alas.name alert_action_set, ala.name alert_action, qpca.status_code, qpca.message action_message, to_char(null) output_variable, to_char(null) output_element, null delete_record, 7 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc, qa_plan_char_action_triggers qpcat, qa_plan_char_actions qpca, qa_actions qa, qa_chars qc_assigned, alr_action_sets alas, alr_actions ala where qp.organization_id=mp.organization_id and qpc.plan_id=qp.plan_id and qpc.char_id=qc.char_id and qpcat.plan_id=qpc.plan_id and qpcat.char_id=qpc.char_id and qpca.plan_char_action_trigger_id=qpcat.plan_char_action_trigger_id and qpca.action_id=qa.action_id and qpca.assigned_char_id=qc_assigned.char_id(+) and qpca.alr_action_set_id=alas.action_set_id(+) and qpca.alr_action_id=ala.action_id(+) and 1=1 union all select null action_, null status_, null message_, null modified_columns_, mp.organization_code, qp.name plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, to_char(null) transaction_description, to_char(null) mandatory_collection, to_char(null) background_collection, to_char(null) transaction_enabled, to_char(null) collection_trigger, to_char(null) trigger_operator, to_char(null) trigger_low_value, to_char(null) trigger_high_value, qc.name element_name, qpc.prompt_sequence, qpc.prompt element_prompt, xxen_util.meaning(decode(qpc.enabled_flag,1,'Y','N'),'YES_NO',0) element_enabled, xxen_util.meaning(decode(qpc.mandatory_flag,1,'Y','N'),'YES_NO',0) element_mandatory, qpc.default_value, xxen_util.meaning(decode(qpc.displayed_flag,1,'Y','N'),'YES_NO',0) displayed, xxen_util.meaning(decode(qpc.read_only_flag,1,'Y','N'),'YES_NO',0) read_only, xxen_util.meaning(decode(qpc.ss_poplist_flag,1,'Y','N'),'YES_NO',0) ss_poplist, xxen_util.meaning(decode(qpc.information_flag,1,'Y','N'),'YES_NO',0) information, qpc.decimal_precision, qpc.uom_code, to_char(null) value_code, to_char(null) value_description, qpcat.trigger_sequence action_trigger_seq, xxen_util.meaning(qpcat.operator,'QA_OPERATOR',700) action_operator, qpcat.low_value_other action_low_value, qpcat.high_value_other action_high_value, qa.description action_name, decode(qpca.assign_type,'F','Formula','S','SQL Statement') assign_type, qc_assigned.name assigned_element, alas.name alert_action_set, ala.name alert_action, qpca.status_code, qpca.message action_message, qpcao.token_name output_variable, qc2.name output_element, null delete_record, 8 row_type, qp.plan_id from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc, qa_plan_char_action_triggers qpcat, qa_plan_char_actions qpca, qa_actions qa, qa_plan_char_action_outputs qpcao, qa_chars qc2, qa_chars qc_assigned, alr_action_sets alas, alr_actions ala where qp.organization_id=mp.organization_id and qpc.plan_id=qp.plan_id and qpc.char_id=qc.char_id and qpcat.plan_id=qpc.plan_id and qpcat.char_id=qpc.char_id and qpca.plan_char_action_trigger_id=qpcat.plan_char_action_trigger_id and qpca.action_id=qa.action_id and qpcao.plan_char_action_id=qpca.plan_char_action_id and qpcao.char_id=qc2.char_id and qpca.assigned_char_id=qc_assigned.char_id(+) and qpca.alr_action_set_id=alas.action_set_id(+) and qpca.alr_action_id=ala.action_id(+) and 1=1 U A xxen_qa_upload.collection_plan xxen_qa_upload.complete_plan_processing_ US QA Collection Plan Upload Upload to create, update, or delete Quality Collection Plans and all child entities. This upload manages: - Collection Plan headers (name, type, dates, multirow flag) - Plan Transactions (transaction assignments, mandatory/background flags) - Collection Triggers (trigger conditions per transaction) - Plan Elements (collection element assignments, prompts, flags) - Element Values (value lookups per element) - Action Triggers (trigger sequences and conditions per element) - Actions (action assignments per trigger, including assign type, assigned element, alert action set and action, status code) - Action Outputs (output variable mappings per action) Multiple actions of the same action type can coexist under one action trigger (e.g. two Assign value actions with different formulas, or two Send email actions with different alert sets). Actions are identified by the natural key (action trigger, action name, assign type, assigned element, alert action set, alert action, status code, action message); changing any of these fields creates a new action rather than updating an existing one. Use Delete=Yes to remove an obsolete action. CAR (Corrective Action Request) actions are not yet supported. Upload Modes ============ Create ------ Opens an empty spreadsheet for entering new Collection Plans and child entities. Create, Update -------------- Downloads existing Collection Plans matching filters for review and update. New rows can be added to create additional plans in the same upload. Row Types ========= The upload uses a hierarchical structure with 8 row types: 1. Plan Header 2. Transaction 3. Collection Trigger 4. Element 5. Element Value 6. Action Trigger 7. Action 8. Action Output Each child row inherits its parent context from the plan_name column. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 mp.organization_code=:p_organization LOV custom Y select mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code US Organization 3 30 1=1 qp.plan_type_code=:p_plan_type LOV custom Y select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='COLLECTION_PLAN_TYPE' and flvv.view_application_id=3 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Plan Type 4 40 1=1 qp.name=:p_plan LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US Collection Plan 5 50 1=1 qp.name>=:p_plan_from LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US Collection Plan From 6 60 1=1 qp.name<=:p_plan_to LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name :$flex$.Collection_Plan_From US Collection Plan To 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ORGANIZATION_CODE LOV custom varchar2 3 Y select mp.organization_id id, mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code Y 6 PLAN_NAME Char varchar2 30 Y 7 PLAN_DESCRIPTION Char varchar2 150 8 PLAN_TYPE LOV custom varchar2 Y select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='COLLECTION_PLAN_TYPE' and flvv.view_application_id=3 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning 9 EFFECTIVE_FROM Date date 10 EFFECTIVE_TO Date date 11 MULTIROW LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 12 TRANSACTION_DESCRIPTION LOV custom varchar2 240 Y select distinct qptv.transaction_number id, qptv.transaction_description value from qa_plan_transactions_v qptv order by qptv.transaction_description select distinct qptv.transaction_number id, qptv.transaction_description value from qa_plan_transactions_v qptv 13 MANDATORY_COLLECTION LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 14 BACKGROUND_COLLECTION LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 15 TRANSACTION_ENABLED LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 16 COLLECTION_TRIGGER LOV custom varchar2 30 Y select qctdv.collection_trigger_id id, qctdv.collection_trigger_description value, null description from qa_txn_collection_triggers_v qctdv where (:$column$.transaction_description is null or qctdv.transaction_number in (select distinct qptv.transaction_number from qa_plan_transactions_v qptv where qptv.transaction_description=:$column$.transaction_description)) order by qctdv.collection_trigger_description 17 TRIGGER_OPERATOR LOV custom varchar2 Y select to_number(flvv.lookup_code) id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='QA_OPERATOR' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) 18 TRIGGER_LOW_VALUE Char varchar2 150 19 TRIGGER_HIGH_VALUE Char varchar2 150 20 ELEMENT_NAME LOV custom varchar2 30 Y Y select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name 21 PROMPT_SEQUENCE Number number 22 ELEMENT_PROMPT Char varchar2 30 23 ELEMENT_ENABLED LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 24 ELEMENT_MANDATORY LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 25 DEFAULT_VALUE Char varchar2 2000 26 DISPLAYED LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 27 READ_ONLY LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 28 SS_POPLIST LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 29 INFORMATION LOV custom varchar2 Y select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning 30 DECIMAL_PRECISION Number number 31 UOM_CODE LOV custom varchar2 3 select muom.uom_code value, muom.unit_of_measure description from mtl_units_of_measure muom where nvl(muom.disable_date,sysdate+1)>sysdate order by muom.uom_code 32 VALUE_CODE Char varchar2 150 33 VALUE_DESCRIPTION Char varchar2 150 34 ACTION_TRIGGER_SEQ Number number 35 ACTION_OPERATOR LOV custom varchar2 Y select to_number(flvv.lookup_code) id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='QA_OPERATOR' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) 36 ACTION_LOW_VALUE Char varchar2 150 37 ACTION_HIGH_VALUE Char varchar2 150 38 ACTION_NAME LOV custom varchar2 80 Y select qa.action_id id, qa.description value, null description from qa_actions qa order by qa.description 39 ASSIGN_TYPE LOV custom varchar2 13 Y select 'F' id, 'Formula' value, null description from dual union all select 'S' id, 'SQL Statement' value, null description from dual 40 ASSIGNED_ELEMENT LOV custom varchar2 30 Y select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name 41 ALERT_ACTION_SET LOV custom varchar2 50 Y select alas.action_set_id id, alas.name value, alas.description from alr_action_sets alas where alas.enabled_flag='Y' order by alas.name select alas.action_set_id id, alas.name value from alr_action_sets alas 42 ALERT_ACTION LOV custom varchar2 80 Y select ala.action_id id, ala.name value, ala.description from alr_actions ala, alr_action_sets alas where ala.application_id=alas.application_id and alas.name=:$column$.alert_action_set order by ala.name 43 STATUS_CODE Char varchar2 30 44 ACTION_MESSAGE Char varchar2 2000 45 OUTPUT_VARIABLE Char varchar2 30 46 OUTPUT_ELEMENT LOV custom varchar2 30 Y select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name 47 DELETE_RECORD LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.plan_name is not null 48 ROW_TYPE Number number Y 49 PLAN_ID Number number Y U 1 p_organization_id in Number ORGANIZATION_CODE U 2 p_plan_name in Char PLAN_NAME U 3 p_plan_description in Char PLAN_DESCRIPTION U 4 p_plan_type in Char PLAN_TYPE U 5 p_effective_from in Date EFFECTIVE_FROM U 6 p_effective_to in Date EFFECTIVE_TO U 7 p_multirow in Char MULTIROW U 8 p_transaction_number in Number TRANSACTION_DESCRIPTION U 9 p_mandatory_collection in Char MANDATORY_COLLECTION U 10 p_background_collection in Char BACKGROUND_COLLECTION U 11 p_transaction_enabled in Char TRANSACTION_ENABLED U 12 p_collection_trigger_id in Number COLLECTION_TRIGGER U 13 p_trigger_operator in Number TRIGGER_OPERATOR U 14 p_trigger_low_value in Char TRIGGER_LOW_VALUE U 15 p_trigger_high_value in Char TRIGGER_HIGH_VALUE U 16 p_element_id in Number ELEMENT_NAME U 17 p_prompt_sequence in Number PROMPT_SEQUENCE U 18 p_element_prompt in Char ELEMENT_PROMPT U 19 p_element_enabled in Char ELEMENT_ENABLED U 20 p_element_mandatory in Char ELEMENT_MANDATORY U 21 p_default_value in Char DEFAULT_VALUE U 22 p_displayed in Char DISPLAYED U 23 p_read_only in Char READ_ONLY U 24 p_ss_poplist in Char SS_POPLIST U 25 p_information in Char INFORMATION U 26 p_decimal_precision in Number DECIMAL_PRECISION U 27 p_uom_code in Char UOM_CODE U 28 p_value_code in Char VALUE_CODE U 29 p_value_description in Char VALUE_DESCRIPTION U 30 p_action_trigger_seq in Number ACTION_TRIGGER_SEQ U 31 p_action_operator in Number ACTION_OPERATOR U 32 p_action_low_value in Char ACTION_LOW_VALUE U 33 p_action_high_value in Char ACTION_HIGH_VALUE U 34 p_action_id in Number ACTION_NAME U 35 p_assign_type in Char ASSIGN_TYPE U 36 p_assigned_element_id in Number ASSIGNED_ELEMENT U 37 p_alert_action_set_id in Number ALERT_ACTION_SET U 38 p_alert_action_id in Number ALERT_ACTION U 39 p_status_code in Char STATUS_CODE U 40 p_action_message in Char ACTION_MESSAGE U 41 p_output_variable in Char OUTPUT_VARIABLE U 42 p_output_element_id in Number OUTPUT_ELEMENT U 43 p_delete_record in Char DELETE_RECORD U 44 status_ out Char STATUS_CODE_ U 45 message_ out Char MESSAGE_ Error SQL Order By organization_code, plan_name, row_type, transaction_description, element_name, prompt_sequence, value_code, action_trigger_seq, action_name, action_message, output_variable Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.mp_organization_code, nvl(qp.name,xu.plan_name) plan_name, qp.description plan_description, xxen_util.meaning(qp.plan_type_code,'COLLECTION_PLAN_TYPE',3) plan_type, qp.effective_from, qp.effective_to, xxen_util.meaning(decode(qp.multirow_flag,1,'Y','N'),'YES_NO',0) multirow, xu.transaction_description, xu.mandatory_collection, xu.background_collection, xu.transaction_enabled, xu.collection_trigger, xu.trigger_operator, xu.trigger_low_value, xu.trigger_high_value, xu.element_name, xu.prompt_sequence, xu.element_prompt, xu.element_enabled, xu.element_mandatory, xu.default_value, xu.displayed, xu.read_only, xu.ss_poplist, xu.information, xu.decimal_precision, xu.uom_code, xu.value_code, xu.value_description, xu.action_trigger_seq, xu.action_operator, xu.action_low_value, xu.action_high_value, xu.action_name, xu.assign_type, xu.assigned_element, xu.alert_action_set, xu.alert_action, xu.status_code, xu.action_message, xu.output_variable, xu.output_element, xu.delete_record, xu.row_type, qp.plan_id from ( select mp.organization_code mp_organization_code, mp.organization_id mp_organization_id, xu.action_, xu.action_high_value, xu.action_low_value, xu.action_message, xu.action_name, xu.action_operator, xu.action_trigger_seq, xu.alert_action, xu.alert_action_set, xu.assign_type, xu.assigned_element, xu.background_collection, xu.collection_trigger, xu.decimal_precision, xu.default_value, xu.delete_record, xu.displayed, xu.element_enabled, xu.element_mandatory, xu.element_name, xu.element_prompt, xu.information, xu.mandatory_collection, xu.message_, xu.modified_columns_, xu.output_element, xu.output_variable, xu.plan_name, xu.prompt_sequence, xu.read_only, xu.row_type, xu.ss_poplist, xu.status_, xu.status_code, xu.transaction_description, xu.transaction_enabled, xu.trigger_high_value, xu.trigger_low_value, xu.trigger_operator, xu.uom_code, xu.value_code, xu.value_description from xxen_qa_collection_plan_9805_u xu, mtl_parameters mp where xu.status_code_='S' and xu.organization_code=mp.organization_code ) xu, --before 12c an outer joined table may reference at most one other table, so the driving tables are wrapped qa_plans qp where xu.mp_organization_id=qp.organization_id(+) and xu.plan_name=qp.name(+)