ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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) result_column, 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, to_number(null) upload_row 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) result_column, 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, to_number(null) upload_row 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) result_column, 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, to_number(null) upload_row 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, upper(qpc.result_column_name) result_column, 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, to_number(null) upload_row 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, upper(qpc.result_column_name) result_column, 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, to_number(null) upload_row 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, upper(qpc.result_column_name) result_column, 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, to_number(null) upload_row 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, upper(qpc.result_column_name) result_column, 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, to_number(null) upload_row 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, upper(qpc.result_column_name) result_column, 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, to_number(null) upload_row 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 Result Column added: a new element takes the result column entered, so a plan copied from another instance keeps its result columns. A comment or sequence element added without a result column takes a COMMENT or SEQUENCE column instead of a CHARACTER column. 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. Result Column shows the column of the quality results table that holds the element's values. A new element takes the result column entered, so a plan copied from another instance keeps its result columns; without one it takes the next free column of its type. The result column of an element already in a plan cannot be changed. 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 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 INV Organization Code (accessible) FA6954EC34B240D9A30C047DDF5BD14D 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code value US Organization 3 30 1=1 qp.plan_type_code=:p_plan_type LOV custom 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 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) order by qp.name US Collection Plan 5 50 1=1 qp.name>=:p_plan_from LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) order by qp.name US Collection Plan From 6 60 1=1 qp.name<=:p_plan_to LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) order by qp.name :$flex$.Collection_Plan_From US Collection Plan To B1BDED18A9A145E18EE25BEF4939047B Default All columns. ENGINATICS 34 ACTION_HIGH_VALUE 33 ACTION_LOW_VALUE 41 ACTION_MESSAGE 35 ACTION_NAME 32 ACTION_OPERATOR 31 ACTION_TRIGGER_SEQ 39 ALERT_ACTION 38 ALERT_ACTION_SET 37 ASSIGNED_ELEMENT 36 ASSIGN_TYPE 10 BACKGROUND_COLLECTION 12 COLLECTION_TRIGGER 26 DECIMAL_PRECISION 21 DEFAULT_VALUE 44 DELETE_RECORD 22 DISPLAYED 5 EFFECTIVE_FROM 6 EFFECTIVE_TO 19 ELEMENT_ENABLED 20 ELEMENT_MANDATORY 16 ELEMENT_NAME 18 ELEMENT_PROMPT 25 INFORMATION 9 MANDATORY_COLLECTION 7 MULTIROW 1 ORGANIZATION_CODE 43 OUTPUT_ELEMENT 42 OUTPUT_VARIABLE 3 PLAN_DESCRIPTION Y 2 PLAN_NAME 4 PLAN_TYPE 17 PROMPT_SEQUENCE 23 READ_ONLY 28 RESULT_COLUMN 24 SS_POPLIST 40 STATUS_CODE 8 TRANSACTION_DESCRIPTION 11 TRANSACTION_ENABLED 15 TRIGGER_HIGH_VALUE 14 TRIGGER_LOW_VALUE 13 TRIGGER_OPERATOR 27 UOM_CODE 29 VALUE_CODE 30 VALUE_DESCRIPTION S Site B1BDED18A9A145E18EE25BEF4939047B 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ORGANIZATION_CODE LOV varchar2 3 INV Organization Code (accessible) FA6954EC34B240D9A30C047DDF5BD14D 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code Organization of the collection plan. Y 6 PLAN_NAME Char varchar2 30 Collection plan the row belongs to. A new plan is stored in upper case. The populated columns decide what a row maintains: Output Variable an action output, Action Name an action, Action Trigger Seq with Action Operator an action trigger, Value Code an element value, Collection Trigger a collection trigger, Transaction Description a plan transaction, Element Name a plan element, otherwise the plan header. Y 7 PLAN_DESCRIPTION Char varchar2 150 Plan header rows only. 8 PLAN_TYPE LOV custom varchar2 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 Plan header rows only. Required to create a plan. 9 EFFECTIVE_FROM Date date Plan header rows only. 10 EFFECTIVE_TO Date date Plan header rows only. 11 MULTIROW LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 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 Collection transaction the plan is assigned to. Collection trigger rows name the transaction they belong to. 13 MANDATORY_COLLECTION LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning Transaction rows only. 14 BACKGROUND_COLLECTION LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning Transaction rows only. 15 TRANSACTION_ENABLED LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning Transaction rows only. 16 COLLECTION_TRIGGER LOV custom varchar2 30 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 Collection trigger of the transaction, with its Trigger Operator and Trigger Low and High Value. 17 TRIGGER_OPERATOR LOV custom varchar2 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 select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name Collection element of the plan. Element value, action trigger, action and action output rows name the element they belong to. 21 PROMPT_SEQUENCE Number number Position of the element in the plan. Defaults to the next free sequence when the element is added. 22 ELEMENT_PROMPT Char varchar2 30 Prompt of the element in this plan. Defaults to the prompt of the collection element. 23 ELEMENT_ENABLED LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 select fl.lookup_code id, fl.meaning value, null description from fnd_lookups fl where fl.lookup_type='YES_NO' order by fl.meaning Defaults to the Mandatory flag of the collection element when the element is added. The plan can be completed only when it has at least one enabled mandatory element. 25 DEFAULT_VALUE Char varchar2 2000 Defaults to the default value of the collection element when the element is added. 26 DISPLAYED LOV varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 varchar2 FND Yes No 9AFB6F38BF9649E195C02E81986C3438 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 Defaults to the decimal precision of the collection element when the element is added. 31 UOM_CODE LOV varchar2 3 INV UOM Code (active, translated) 8AEDD3EE31DF40488E61A436D2B0B86A Y select muomv.uom_code id, muomv.uom_code value, muomv.unit_of_measure description from mtl_units_of_measure_vl muomv where muomv.disable_date is null order by 1 Defaults to the UOM of the collection element when the element is added. 32 RESULT_COLUMN LOV custom varchar2 30 select x.column_value value, null description from table(xxen_qa_upload.result_columns( ( select max(qp.plan_id) keep (dense_rank first order by decode(qp.name,:$column$.plan_name,1,2)) from mtl_parameters mp, qa_plans qp where mp.organization_code=:$column$.organization_code and mp.organization_id=qp.organization_id and qp.name in (:$column$.plan_name,upper(:$column$.plan_name)) ), (select qc.char_id from qa_chars qc where qc.name=:$column$.element_name) )) x Column of the quality results table that holds the element's values: CHARACTER1-100, COMMENT1-5, SEQUENCE1-15, or the fixed column of a seeded element. A new element takes the column entered, and the next free column of its type when it is left blank. The result column of an element already in the plan cannot be changed. Y 33 VALUE_CODE Char varchar2 150 Short code of a value of the element that applies to this plan only. 34 VALUE_DESCRIPTION Char varchar2 150 Description of the element value. 35 ACTION_TRIGGER_SEQ Number number Sequence of the action trigger of the element. Action and action output rows name their trigger by this sequence. 36 ACTION_OPERATOR LOV custom varchar2 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) Condition of the action trigger, with Action Low and High Value. 37 ACTION_LOW_VALUE Char varchar2 150 38 ACTION_HIGH_VALUE Char varchar2 150 39 ACTION_NAME LOV custom varchar2 80 select qa.action_id id, qa.description value, null description from qa_actions qa order by qa.description Action of the action trigger. An action is identified by its trigger, action name, assign type, assigned element, alert action set, alert action, status code and action message, so changing any of these creates a new action. 40 ASSIGN_TYPE LOV custom varchar2 13 select 'F' id, 'Formula' value, null description from dual union all select 'S' id, 'SQL Statement' value, null description from dual 41 ASSIGNED_ELEMENT LOV custom varchar2 30 select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name 42 ALERT_ACTION_SET LOV custom varchar2 50 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 43 ALERT_ACTION LOV custom varchar2 80 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 44 STATUS_CODE Char varchar2 30 45 ACTION_MESSAGE Char varchar2 2000 46 OUTPUT_VARIABLE Char varchar2 30 Token of the action message whose value is taken from the Output Element. 47 OUTPUT_ELEMENT LOV custom varchar2 30 select qc.char_id id, qc.name value, qc.prompt description from qa_chars qc where qc.enabled_flag=1 order by qc.name 48 DELETE_RECORD LOV custom varchar2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.plan_name is not null Yes deletes what the row maintains. On a plan header row it deletes the plan with all its transactions, elements and actions. 49 ROW_TYPE Number number Y 50 PLAN_ID Number number Y 51 UPLOAD_ROW 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_result_column in Char Y RESULT_COLUMN U 29 p_value_code in Char VALUE_CODE U 30 p_value_description in Char VALUE_DESCRIPTION U 31 p_action_trigger_seq in Number ACTION_TRIGGER_SEQ U 32 p_action_operator in Number ACTION_OPERATOR U 33 p_action_low_value in Char ACTION_LOW_VALUE U 34 p_action_high_value in Char ACTION_HIGH_VALUE U 35 p_action_id in Number ACTION_NAME U 36 p_assign_type in Char ASSIGN_TYPE U 37 p_assigned_element_id in Number ASSIGNED_ELEMENT U 38 p_alert_action_set_id in Number ALERT_ACTION_SET U 39 p_alert_action_id in Number ALERT_ACTION U 40 p_status_code in Char STATUS_CODE U 41 p_action_message in Char ACTION_MESSAGE U 42 p_output_variable in Char OUTPUT_VARIABLE U 43 p_output_element_id in Number OUTPUT_ELEMENT U 44 p_delete_record in Char DELETE_RECORD U 45 status_ out Char STATUS_CODE_ U 46 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, nvl((select upper(qpc.result_column_name) from qa_plan_chars qpc, qa_chars qc where qp.plan_id=qpc.plan_id and xu.element_name=qc.name and qpc.char_id=qc.char_id),xu.result_column) result_column, 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, xu.upload_row from ( select mp.organization_code mp_organization_code, coalesce((select qp2.plan_id from qa_plans qp2 where xu.plan_name=qp2.name and mp.organization_id=qp2.organization_id),(select qp2.plan_id from qa_plans qp2 where upper(xu.plan_name)=qp2.name and mp.organization_id=qp2.organization_id)) qp_plan_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.result_column, xu.value_code, xu.value_description, xu.upload_row from xxen_qa_collection_plan_9805_u xu, mtl_parameters mp where xu.status_code_='S' and xu.organization_code=mp.organization_code ) xu, --a new plan is stored in upper case, and an outer join cannot compare with a subquery qa_plans qp where xu.qp_plan_id=qp.plan_id(+)