ONT Transaction Types and Line WF Processes

Description
Categories: Enginatics, Toolkit - Setup & Support
Repository: Github Columns: Operating Unit, Transaction Type, Description, Sales Document Type, Order Category, Transaction Type Code, Fulfillment Flow, Negotiation Flow, Start Date, End Date ...
Master data report showing the setup of order management transaction types and associated line types and workflow processes.
select
haouv.name operating_unit,
ottt.name transaction_type,
ottt.description,
decode(otta.sales_document_type_code,'B','Sales Agreement','O','Sales Order') sales_document_type,
initcap(otta.order_category_code) order_category,
initcap(otta.transaction_type_code) transaction_type_code,
nvl(wav1.display_name,owa1.process_name) fulfillment_flow,
nvl(wav2.display_name,owa2.process_name) negotiation_flow,
otta.start_date_active start_date,
otta.end_date_active end_date,
xtv.template_name layout_template,
oktta.description contract_template,
&columns
ottt2.name default_return_line_type,
ottt3.name default_order_line_type,
qlht.name price_list,
occr1.name ordering_cr_check_rule,
occr2.name packing_cr_check_rule,
occr3.name picking_cr_check_rule,
occr4.name shipping_cr_check_rule,
ood.organization_name warehouse,
otta.shipment_priority_code shipment_priority,
xxen_util.meaning(otta.fob_point_code,'FOB',222) fob,
xxen_util.meaning(otta.demand_class_code,'DEMAND_CLASS',3) demand_class,
xxen_util.meaning(otta.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
rr1.name invoicing_rule,
rr2.name accounting_rule,
rctta.name receivables_transaction_type,
gcck.concatenated_segments cost_of_goods_sold_account,
otta.currency_code currency
from
hr_all_organization_units_vl haouv,
oe_transaction_types_all otta,
org_organization_definitions ood,
qp_list_headers_tl qlht,
oe_credit_check_rules occr1,
oe_credit_check_rules occr2,
oe_credit_check_rules occr3,
oe_credit_check_rules occr4,
ra_rules rr1,
ra_rules rr2,
ra_cust_trx_types_all rctta,
gl_code_combinations_kfv gcck,
(select owa.* from oe_workflow_assignments owa where owa.line_type_id is null and sysdate between owa.start_date_active and nvl(owa.end_date_active,sysdate)) owa1,
(select owa.* from oe_workflow_assignments owa where owa.line_type_id is null and sysdate between owa.start_date_active and nvl(owa.end_date_active,sysdate)) owa2,
(select owa.* from oe_workflow_assignments owa where '&enable_line'='Y' and owa.line_type_id is not null and nvl(owa.wf_item_type,'OEOL')='OEOL') owa3,
(select wav.* from wf_activities_vl wav where wav.type='PROCESS' and wav.runnable_flag='Y' and sysdate between wav.begin_date and nvl(wav.end_date,sysdate)) wav1,
(select wav.* from wf_activities_vl wav where wav.type='PROCESS' and wav.runnable_flag='Y' and sysdate between wav.begin_date and nvl(wav.end_date,sysdate)) wav2,
(select wav.* from wf_activities_vl wav where wav.type='PROCESS' and wav.runnable_flag='Y' and sysdate between wav.begin_date and nvl(wav.end_date,sysdate)) wav3,
oe_transaction_types_tl ottt,
oe_transaction_types_tl ottt1,
oe_transaction_types_tl ottt2,
oe_transaction_types_tl ottt3,
xdo_templates_vl xtv,
okc_terms_templates_all oktta
where
1=1 and
haouv.organization_id=otta.org_id and
otta.warehouse_id=ood.organization_id(+) and
otta.price_list_id=qlht.list_header_id(+) and
qlht.language(+)=userenv('lang') and
otta.entry_credit_check_rule_id=occr1.credit_check_rule_id(+) and
otta.packing_credit_check_rule_id=occr2.credit_check_rule_id(+) and
otta.picking_credit_check_rule_id=occr3.credit_check_rule_id(+) and
otta.shipping_credit_check_rule_id=occr4.credit_check_rule_id(+) and
otta.invoicing_rule_id=rr1.rule_id(+) and
otta.accounting_rule_id=rr2.rule_id(+) and
otta.cust_trx_type_id=rctta.cust_trx_type_id(+) and
otta.org_id=rctta.org_id(+) and
otta.cost_of_goods_sold_account=gcck.code_combination_id(+) and
decode(otta.transaction_type_code,'ORDER',otta.transaction_type_id)=owa1.order_type_id(+) and
decode(otta.transaction_type_code,'ORDER',otta.transaction_type_id)=owa2.order_type_id(+) and
decode(otta.transaction_type_code,'ORDER',otta.transaction_type_id)=owa3.order_type_id(+) and
decode(otta.transaction_type_code,'ORDER',decode(otta.sales_document_type_code,'B','OEBH','O','OEOH'))=owa1.wf_item_type(+) and
decode(otta.transaction_type_code,'ORDER','OENH')=owa2.wf_item_type(+) and
owa1.process_name=wav1.name(+) and
owa2.process_name=wav2.name(+) and
owa3.process_name=wav3.name(+) and
owa1.wf_item_type =wav1.item_type(+) and
owa2.wf_item_type=wav2.item_type(+) and
wav3.item_type(+)='OEOL' and
otta.transaction_type_id=ottt.transaction_type_id and
otta.default_inbound_line_type_id=ottt1.transaction_type_id(+) and
otta.default_outbound_line_type_id=ottt2.transaction_type_id(+) and
owa3.line_type_id=ottt3.transaction_type_id(+) and
ottt.language(+)=userenv('lang') and
ottt1.language(+)=userenv('lang') and
ottt2.language(+)=userenv('lang') and
ottt3.language(+)=userenv('lang') and
otta.layout_template_id=xtv.template_id(+) and
otta.contract_template_id=oktta.template_id(+)
order by
haouv.name,
otta.transaction_type_code desc,
otta.order_category_code,
otta.sales_document_type_code,
ottt.name,
ottt3.name,
owa3.item_type_code,
nvl(wav3.display_name,owa3.process_name),
owa3.start_date_active desc
Parameter Name SQL text Validation
Show assigned Line Flows
ottt3.name line_type,
xxen_util.meaning(owa3.item_type_code,'WF_ASSIGN_ITEM_TYPES',660) line_item_type,
nvl(wav3.display_name,owa3.process_name) line_process_name,
owa3.start_date_active line_start_date,
owa3.end_date_active line_end_date,
LOV
Operating Unit
haouv.name=:operating_unit
LOV
Show assigned Line Flows
Y
Download
 
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: