ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 992AA5F2B07E4184BB14EBDBDABA5E35Y select x.operating_unit, x.customer, x.customer_number, x.order_number, x.quote_number, x.source_type, x.source_document, x.type, x.order_type, x.customer_po, x.sales_agreement_number, x.sales_agreement_line, hp1.party_name ship_to_customer, hca1.account_number ship_to_customer_number, hcsua1.location ship_to_location, (select hz_format_pub.format_address(hps1.location_id,null,null,' , ') from dual) ship_to_address, ftv1.territory_short_name ship_to_country, coalesce(hcsua1.tax_reference,hp1.tax_reference,hp1.jgzz_fiscal_code) ship_to_tax_reference, hp2.party_name bill_to_customer, hca2.account_number bill_to_customer_number, hcsua2.location bill_to_location, (select hz_format_pub.format_address(hps2.location_id,null,null,' , ') from dual) bill_to_address, ftv2.territory_short_name bill_to_country, coalesce(hcsua2.tax_reference,hp2.tax_reference,hp2.jgzz_fiscal_code) bill_to_tax_reference, x.ordered_date, &o2c_order_columns x.price_list, x.salesperson, x.invoice_salesperson, &osc_columns x.order_source, x.order_source_reference, x.header_status, x.currency, x.subtotal, x.tax, nvl(x.line_charges_total,0) + nvl(x.header_charges,0) charges, nvl(x.subtotal,0) + nvl(x.tax,0) + nvl(x.line_charges_total,0) + nvl(x.header_charges,0) total, x.payment_terms, x.invoice_class, x.invoice_type, x.invoice_number, x.invoice_date, x.invoice_gl_date, x.invoice_status, x.invoice_currency, x.invoice_line, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.invoice_amount else null end invoice_amount, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.invoice_accounted_amount else null end invoice_accounted_amount, &o2c_columns x.warehouse, x.shipping_method, x.line_set, x.freight_terms, x.fob, x.shipment_priority, x.shipping_instructions, x.packing_instructions, x.payment_type, x.line, x.shipment_number, x.line_type, x.line_status, x.cancelled_flag, x.cancel_date, x.cancelled_by, x.cancel_reason, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.cancelled_quantity else null end cancelled_quantity, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.cancelled_amount else null end cancelled_amount, x.item, x.description, x.item_type, &category_columns x.uom, x.list_price, x.discounted_price, x.unit_selling_price, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.quantity else null end quantity, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.extended_price else null end extended_price, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.line_charges else null end line_charges, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.tax_amount else null end tax_amount, x.tax_code, x.calculate_price_flag, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.pricing_quantity else null end pricing_quantity, x.pricing_uom, x.pricing_date, x.request_date, x.promise_date, x.schedule_ship_date, x.override_atp_date_code, x.actual_shipment_date, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.shipped_quantity else null end shipped_quantity, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 then x.invoiced_quantity else null end invoiced_quantity, x.shippable_flag, x.ship_set, wnd.name delivery, wda.next_step, wda.release_status, x.project, x.task, &dff_columns2 x.header_created_by, x.header_creation_date, x.header_last_updated_by, x.header_last_update_date, x.line_created_by, x.line_creation_date, x.line_last_updated_by, x.line_last_update_date, x.order_category, x.line_category, x.header_id, x.line_id, x.line_number, x.line_days_late, x.line_quantity_short, xxen_util.meaning(x.line_shipped_flag,'YES_NO',0) line_shipped, x.order_date_type_code, x.split_line, x.orig_line_id, x.orig_line_promise_date, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 and x.line_id = x.orig_line_id then x.orig_line_quantity else null end orig_line_quantity, x.orig_line_delivery_lead_time, x.orig_line_actual_shipment_date, case when row_number() over (partition by x.line_id order by wnd.name nulls last) = 1 and x.line_id = x.orig_line_id then x.orig_line_tot_shipped_quantity else null end orig_line_tot_shipped_quantity, nvl(xxen_util.meaning(x.deliv_in_full,'YES_NO',0),'N/A') line_dif, nvl(xxen_util.meaning(x.deliv_on_time,'YES_NO',0),'N/A') line_dot, nvl(xxen_util.meaning(decode(x.deliv_in_full||x.deliv_on_time,null,null,'YY','Y','N'),'YES_NO',0),'N/A') line_difot, nvl(xxen_util.meaning(min(x.deliv_in_full) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dif, nvl(xxen_util.meaning(min(x.deliv_on_time) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dot, nvl(xxen_util.meaning(decode(min(x.deliv_in_full) over (partition by x.header_id)||min(x.deliv_on_time) over (partition by x.header_id),null,null,'YY','Y','N'),'YES_NO',0),'N/A') orders_difot from ( select /*+ push_pred(oolh) push_pred(oolh2) push_pred(wda) push_pred(oola) push_pred(rctlgda) */ distinct haouv.name operating_unit, hp.party_name customer, hca.account_number customer_number, ooha.order_number, nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) quote_number, decode(ooha.source_document_type_id,10,'Requisitions',2,'Orders',16,'Quotes',7,'Incidents',(select oos0.name from oe_order_sources oos0 where ooha.source_document_type_id=oos0.order_source_id)) source_type, case ooha.source_document_type_id when 10 then (select prha.segment1 from po_requisition_headers_all prha where ooha.source_document_id=prha.requisition_header_id) when 2 then (select to_char(ooha0.order_number) from oe_order_headers_all ooha0 where ooha.source_document_id=ooha0.header_id) when 16 then (select aqha.quote_number||':'||aqha.quote_version from aso_quote_headers_all aqha where ooha.source_document_id=aqha.quote_header_id) when 7 then (select ciab.incident_number from cs_incidents_all_b ciab where ooha.source_document_id=ciab.incident_id) end source_document, decode(ooha.transaction_phase_code,'N','Quote','Order') type, ottt.name order_type, nvl(oola.cust_po_number,ooha.cust_po_number) customer_po, oola.blanket_number sales_agreement_number, oola.blanket_line_number sales_agreement_line, xxen_util.client_time(ooha.ordered_date) ordered_date, (select qlhv.name from qp_list_headers_vl qlhv where ooha.price_list_id=qlhv.list_header_id) price_list, jrrev.resource_name salesperson, max(jrrev2.resource_name) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_salesperson, oos.name order_source, ooha.orig_sys_document_ref order_source_reference, xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) header_status, ooha.transactional_curr_code currency, oola.ord_subtotal subtotal, oola.ord_tax tax, oola.ord_line_charges_total line_charges_total, (select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where ooha.header_id=opa.header_id and opa.line_id is null and opa.list_line_type_code='FREIGHT_CHARGE' and opa.applied_flag='Y') header_charges, (select rtv.name from ra_terms_vl rtv where nvl(oola.payment_term_id,ooha.payment_term_id)=rtv.term_id) payment_terms, xxen_util.meaning(max(rctta.type) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6),'INV/CM/ADJ',222) invoice_class, max(rctta.name) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_type, max(rcta.trx_number) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_number, max(rcta.trx_date) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_date, max(rctlgda0.gl_date) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_gl_date, xxen_util.meaning(max(rcta.status_trx) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6),'PAYMENT_SCHEDULE_STATUS',222) invoice_status, (select /*+ push_pred(x) */ distinct listagg(x.line_number,', ') within group (order by x.line_number) over () from (select rctla.interface_line_context, rctla.interface_line_attribute6, rctla.line_number, sum(lengthb(rctla.line_number)+2) over (partition by rctla.interface_line_attribute6 order by rctla.line_number rows between unbounded preceding and current row) len from ra_customer_trx_lines_all rctla where rctla.line_type != 'FREIGHT' ) x where x.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') and x.interface_line_attribute6=to_char(oola.line_id) and x.len <= 4000 ) invoice_line, sum(rctla.extended_amount) over (partition by rctla.interface_line_attribute6) invoice_amount, max(rcta.invoice_currency_code) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_currency, sum(rctlgda.acctd_amount) over (partition by rctla.interface_line_attribute6) invoice_accounted_amount, (select mp.organization_code from mtl_parameters mp where nvl(oola.ship_from_org_id,ooha.ship_from_org_id)=mp.organization_id) warehouse, (select wcv.carrier_name from wsh_carriers_v wcv where oola.freight_carrier_code=wcv.freight_code) freight_carrier, xxen_util.meaning(nvl(oola.shipping_method_code,ooha.shipping_method_code),'SHIP_METHOD',3) shipping_method, xxen_util.meaning(ooha.customer_preference_set_code,'REQUEST_DATE_TYPE',660) line_set, xxen_util.meaning(nvl(oola.freight_terms_code,ooha.freight_terms_code),'FREIGHT_TERMS',660) freight_terms, xxen_util.meaning(nvl(oola.fob_point_code,ooha.fob_point_code),'FOB',222) fob, xxen_util.meaning(nvl(oola.shipment_priority_code,ooha.shipment_priority_code),'SHIPMENT_PRIORITY',660) shipment_priority, nvl(oola.shipping_instructions,ooha.shipping_instructions) shipping_instructions, nvl(oola.packing_instructions,ooha.packing_instructions) packing_instructions, xxen_util.meaning(nvl(oola.payment_type_code,ooha.payment_type_code),'PAYMENT TYPE',660) payment_type, rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line, ottt2.name line_type, xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status, msiv.concatenated_segments item, msiv.description, xxen_util.meaning(oola.item_type_code,'ITEM_TYPE',660) item_type, oola.ordered_quantity quantity, oola.order_quantity_uom uom, oola.unit_list_price list_price, oola.discounted_price, oola.unit_selling_price, oola.extended_price, oola.line_charges, oola.tax_code, oola.tax_amount, xxen_util.meaning(oola.calculate_price_flag,'CALCULATE_PRICE_FLAG',660) calculate_price_flag, oola.pricing_quantity, oola.pricing_quantity_uom pricing_uom, oola.pricing_date, xxen_util.client_time(oola.request_date) request_date, xxen_util.client_time(oola.promise_date) promise_date, xxen_util.client_time(oola.schedule_ship_date) schedule_ship_date, xxen_util.meaning(oola.override_atp_date_code,'OVERRIDE_ATP_DATE_CODE',660) override_atp_date_code, xxen_util.client_time(oola.actual_shipment_date) actual_shipment_date, oola.shipped_quantity, oola.invoiced_quantity, xxen_util.yes(oola.shippable_flag) shippable_flag, (select os.set_name from oe_sets os where oola.ship_set_id=os.set_id) ship_set, xxen_util.meaning(case when ooha.cancelled_flag='Y' or oola.cancelled_flag='Y' then 'Y' end,'YES_NO',0) cancelled_flag, xxen_util.client_time(oolh.hist_creation_date) cancel_date, xxen_util.user_name(oolh.hist_created_by) cancelled_by, xxen_util.meaning(oer.reason_code,'CANCEL_CODE',660) cancel_reason, decode(oola.cancelled_flag,'Y',oola.cancelled_quantity) cancelled_quantity, decode(oola.cancelled_flag,'Y',oola.cancelled_quantity)*oola.unit_selling_price cancelled_amount, ppa.project_number project, pt.task_number task, &dff_columns xxen_util.user_name(ooha.created_by) header_created_by, xxen_util.client_time(ooha.creation_date) header_creation_date, xxen_util.user_name(ooha.last_updated_by) header_last_updated_by, xxen_util.client_time(ooha.last_update_date) header_last_update_date, xxen_util.user_name(oola.created_by) line_created_by, xxen_util.client_time(oola.creation_date) line_creation_date, xxen_util.user_name(oola.last_updated_by) line_last_updated_by, xxen_util.client_time(oola.last_update_date) line_last_update_date, xxen_util.meaning(ooha.order_category_code,'ORDER_CATEGORY',660) order_category, xxen_util.meaning(oola.line_category_code,'ORDER_CATEGORY',660) line_category, ooha.header_id, oola.line_number, oola.shipment_number, oola.option_number, oola.component_number, oola.service_number, oola.line_id, nvl(oola.ship_to_org_id,ooha.ship_to_org_id) ship_to_org_id, nvl(oola.invoice_to_org_id,ooha.invoice_to_org_id) invoice_to_org_id, &o2c_columns_x ooha.order_date_type_code, oola.split_line, oola.orig_line_id_ orig_line_id, case when oola.ordered_quantity<=0 or oola.is_shippable='N' then null when oola.shipped_quantity<0 then 'Y' else 'N' end line_shipped_flag, case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(trunc(decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',nvl(oola.actual_shipment_date,sysdate),nvl(oola.actual_shipment_date,sysdate) + nvl(oola.delivery_lead_time,0)))-trunc(oola.promise_date),0) end line_days_late, case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(oola.ordered_quantity-nvl(oola.shipped_quantity,0),0) end line_quantity_short, coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time,0) orig_line_delivery_lead_time, xxen_util.client_time(trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date))) orig_line_promise_date, oola.orig_line_quantity, xxen_util.client_time(trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date))) orig_line_actual_shipment_date, oola.orig_line_tot_shipped_quantity, case when oola.orig_line_quantity<=0 or oola.is_shippable='N' then null when oola.orig_line_quantity<=nvl(oola.orig_line_tot_shipped_quantity,0) then 'Y' else 'N' end deliv_in_full, case when oola.orig_line_quantity<=0 or oola.is_shippable='N' then null when trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date,coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date)+1)) + decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',0,nvl(coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time),0)) <= trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date)) then 'Y' else 'N' end deliv_on_time, oola.inventory_item_id, oola.ship_from_org_id from hr_all_organization_units_vl haouv, oe_order_headers_all ooha, ( select nvl(oola.orig_line_id,oola.line_id) orig_line_id_, sum(oola.ordered_quantity) over (partition by oola.header_id,nvl(oola.orig_line_id,oola.line_id)) orig_line_quantity, sum(oola.shipped_quantity) over (partition by oola.header_id,nvl(oola.orig_line_id,oola.line_id)) orig_line_tot_shipped_quantity, sum(decode(oola.cancelled_flag,'N',oola.extended_price)) over (partition by oola.header_id) ord_subtotal, sum(decode(oola.cancelled_flag,'N',oola.tax_amount)) over (partition by oola.header_id) ord_tax, sum(decode(oola.cancelled_flag,'N',oola.line_charges)) over (partition by oola.header_id) ord_line_charges_total, oola.* from ( select nvl( (select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='NEWPRICE' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'), --new price oola.unit_list_price- nvl(oola.unit_list_price*(select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand)/100 from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='%' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'),0)- --percentage discount nvl((select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.arithmetic_operator='AMT' and opa.list_line_type_code='DIS' and opa.applied_flag='Y'),0) --absolute amount discount ) discounted_price, decode(oola.line_category_code,'RETURN',-1,1)*oola.unit_selling_price*oola.ordered_quantity extended_price, decode(oola.line_category_code,'RETURN',-1,1)*oola.tax_value tax_amount, ( select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*decode(opa.arithmetic_operator,'LUMPSUM',case when oola.ordered_quantity>0 then opa.operand end,oola.ordered_quantity*opa.adjusted_amount)) line_charges from oe_price_adjustments opa where oola.line_id=opa.line_id and opa.list_line_type_code='FREIGHT_CHARGE' and opa.applied_flag='Y' ) line_charges, max(oola.open_flag) over (partition by oola.header_id) max_open_flag, sum(decode(oola.line_category_code,'ORDER',oola.shipped_quantity,0)) over (partition by oola.header_id) ord_shipped_qty, case when oola.shippable_flag='Y' and oola.line_category_code<>'RETURN' and oola.cancelled_flag='N' then 'Y' end is_shippable, xxen_util.meaning(nvl2(oola.split_from_line_id,'Y',null),'YES_NO',0) split_line, case when oola.split_from_line_id is not null then xxen_util.orig_order_line_id(oola.split_from_line_id) end orig_line_id, oola.* from oe_order_lines_all oola where 2=2 and oola.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) oola ) oola, oe_transaction_types_tl ottt, oe_transaction_types_tl ottt2, mtl_system_items_vl msiv, hz_cust_accounts hca, hz_parties hp, oe_order_sources oos, jtf_rs_salesreps jrs, jtf_rs_salesreps jrs2, jtf_rs_resource_extns_vl jrrev, jtf_rs_resource_extns_vl jrrev2, ( select ppa.project_id, ppa.segment1 project_number from pa_projects_all ppa union select psm.project_id, psm.project_number from pjm_seiban_numbers psm ) ppa, pa_tasks pt, ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, ra_cust_trx_types_all rctta, (select rctlgda.* from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.account_class='REC' and rctlgda.latest_rec_flag='Y') rctlgda0, (select rctlgda.customer_trx_line_id, sum(rctlgda.acctd_amount) acctd_amount from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.account_set_flag='N' group by rctlgda.customer_trx_line_id) rctlgda, ( select distinct oolh.line_id, min(oolh.hist_creation_date) over (partition by oolh.line_id) hist_creation_date, min(oolh.hist_created_by) keep (dense_rank first order by oolh.hist_creation_date) over (partition by oolh.line_id) hist_created_by, min(oolh.reason_id) keep (dense_rank first order by oolh.hist_creation_date) over (partition by oolh.line_id) reason_id from oe_order_lines_history oolh where oolh.hist_type_code='CANCELLATION' ) oolh, ( select distinct oolh.line_id, min(oolh.delivery_lead_time) keep (dense_rank first order by oolh.hist_creation_date) over (partition by oolh.line_id) delivery_lead_time, min(oolh.promise_date) keep (dense_rank first order by oolh.hist_creation_date) over (partition by oolh.line_id) promise_date, min(oolh.actual_shipment_date) keep (dense_rank first order by oolh.hist_creation_date) over (partition by oolh.line_id) actual_shipment_date from oe_order_lines_history oolh ) oolh2, oe_reasons oer, oe_order_lines_all oola2 where 1=1 and haouv.organization_id=ooha.org_id and ooha.sold_to_org_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and ooha.order_type_id=ottt.transaction_type_id(+) and ottt.language(+)=userenv('lang') and ooha.order_source_id=oos.order_source_id(+) and ooha.header_id=oola.header_id(+) and oola.line_type_id=ottt2.transaction_type_id(+) and ottt2.language(+)=userenv('lang') and oola.inventory_item_id=msiv.inventory_item_id(+) and oola.ship_from_org_id=msiv.organization_id(+) and ooha.salesrep_id=jrs.salesrep_id(+) and ooha.org_id=jrs.org_id(+) and jrs.resource_id=jrrev.resource_id(+) and oola.project_id=ppa.project_id(+) and oola.task_id=pt.task_id(+) and to_char(oola.line_id)=rctla.interface_line_attribute6(+) and rctla.interface_line_context(+) in ('INTERCOMPANY','ORDER ENTRY') and rctla.customer_trx_id=rcta.customer_trx_id(+) and rcta.cust_trx_type_id=rctta.cust_trx_type_id(+) and rcta.org_id=rctta.org_id(+) and rcta.customer_trx_id=rctlgda0.customer_trx_id(+) and rctla.customer_trx_line_id=rctlgda.customer_trx_line_id(+) and rcta.primary_salesrep_id=jrs2.salesrep_id(+) and rcta.org_id=jrs2.org_id(+) and jrs2.resource_id=jrrev2.resource_id(+) and oola.line_id=oolh.line_id(+) and oolh.reason_id=oer.reason_id(+) and oola.orig_line_id=oola2.line_id(+) and oola.orig_line_id=oolh2.line_id(+) ) x, ( select distinct wdd.source_line_id, wda.delivery_id, xxen_util.wsh_next_step(wdd.container_flag,wdd.released_status,wdd.source_code,wdd.inv_interfaced_flag,wdd.oe_interfaced_flag,null,null,wdd.move_order_line_id) next_step, xxen_util.meaning(wdd.released_status,'PICK_STATUS',665) release_status from wsh_delivery_details wdd, wsh_delivery_assignments wda where wdd.source_code='OE' and wdd.delivery_detail_id=wda.delivery_detail_id ) wda, wsh_new_deliveries wnd, hz_cust_site_uses_all hcsua1, hz_cust_site_uses_all hcsua2, hz_cust_acct_sites_all hcasa1, hz_cust_acct_sites_all hcasa2, hz_cust_accounts hca1, hz_cust_accounts hca2, hz_parties hp1, hz_parties hp2, hz_party_sites hps1, hz_party_sites hps2, hz_locations hl1, hz_locations hl2, fnd_territories_vl ftv1, fnd_territories_vl ftv2 &osc_from where x.line_id=wda.source_line_id(+) and wda.delivery_id=wnd.delivery_id(+) and x.ship_to_org_id=hcsua1.site_use_id(+) and x.invoice_to_org_id=hcsua2.site_use_id(+) and hcsua1.cust_acct_site_id=hcasa1.cust_acct_site_id(+) and hcsua2.cust_acct_site_id=hcasa2.cust_acct_site_id(+) and hcasa1.cust_account_id=hca1.cust_account_id(+) and hcasa2.cust_account_id=hca2.cust_account_id(+) and hca1.party_id=hp1.party_id(+) and hca2.party_id=hp2.party_id(+) and hcasa1.party_site_id=hps1.party_site_id(+) and hcasa2.party_site_id=hps2.party_site_id(+) and hps1.location_id=hl1.location_id(+) and hps2.location_id=hl2.location_id(+) and hl1.country=ftv1.territory_code(+) and hl2.country=ftv2.territory_code(+) &osc_where order by x.operating_unit, x.customer_number, x.order_number, x.line_number, x.shipment_number, nvl(x.option_number,-1), nvl(x.component_number,-1), nvl(x.service_number,-1), wnd.name nulls last Added the Show Sales Credits parameter (one row per line or header sales credit with salesperson, credit type, quota, percent and credited amount), the Sales Agreement Number parameter and the sales agreement number and line columns. #,##0.00;[Red]-#,##0.00 US ONT Orders and Lines 11i Detail Sales Order or Quote header report with line item details including status, cost, project and shipping information. Show Sales Credits adds one row per sales credit of each line, taken from the line, or from the order header for lines without sales credits of their own. Credited Amount is the line extended price times the credit percent and is shown once per line and credit, so it can be summed by salesperson; the other amount columns stay on one row per line. Show Order to Cash Details adds the booked date and open holds of each line, and for the line's latest invoice the due date, the amount still due, the date it was fully paid, the latest receipt applied, and the dates it was transferred to the general ledger (Accounted Date) and its journal posted (GL Posted Date, shown when the Receivables journal source imports journal references). Amount Due Remaining is shown on one row per invoice, so it can be summed. To follow lines until they are paid, leave Open only empty, so that closed orders with unpaid invoices are included. Enginatics &category_columns &dff_columns &dff_columns2 &o2c_columns &o2c_columns_x &o2c_order_columns &osc_columns &osc_from &osc_where 1=1 2=2 1 -10 2=2 (oola.ship_from_org_id, oola.inventory_item_id) in ( select distinct xxen_util.instring(dbms_lob.substr(xrrpv.value),'|',1) organization_id, xxen_util.instring(dbms_lob.substr(xrrpv.value),'|',2) inventory_item_id from xxen_report_run_param_values xrrpv where xrrpv.parameter_id in (select xrpv.parameter_id from xxen_report_parameters_v xrpv where xrpv.parameter_name='organization_id|inventory_item_id') and xrrpv.run_id=:run_id ) Char US organization_id|inventory_item_id 2 1=1 ooha.header_id=oola.header_id US organization_id|inventory_item_id 3 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 4 20 1=1 ooha.order_number=:order_number LOV custom Y Y select ooha.order_number value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:$flex$.operating_unit)) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc US Order Number 5 30 1=1 ( ooha.quote_number=:quote_number or ooha.orig_sys_document_ref like :quote_number||'%' and ooha.source_document_type_id=16 ) LOV custom Y Y select nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where (ooha.quote_number is not null or ooha.orig_sys_document_ref is not null and ooha.source_document_type_id=16) and (:$flex$.operating_unit is null or ooha.org_id=(select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:$flex$.operating_unit)) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by ooha.order_number desc US Quote Number 6 40 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer Name 7 50 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Account Number 8 60 1=1 nvl(ooha.transaction_phase_code,'F')='F' LOV custom Y select 'Order' value, '' description from dual union all select 'Quote' value, '' description from dual Order US Type 9 1=1 ooha.transaction_phase_code='N' Quote US Type 10 70 1=1 ooha.order_category_code=xxen_util.lookup_code(:order_category,'ORDER_CATEGORY',660) LOV custom Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='ORDER_CATEGORY' and flvv.view_application_id=660 and flvv.security_group_id=0 order by flvv.meaning US Order Category 11 80 1=1 oola.line_category_code=xxen_util.lookup_code(:line_category,'ORDER_CATEGORY',660) LOV custom Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_code in ('ORDER','RETURN') and flvv.lookup_type='ORDER_CATEGORY' and flvv.view_application_id=660 and flvv.security_group_id=0 order by flvv.meaning US Line Category 12 90 1=1 ottt.name=:order_type LOV custom Y select ottt.name value, xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description from oe_transaction_types_all otta, oe_transaction_types_tl ottt where (:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and otta.transaction_type_code='ORDER' and otta.transaction_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by decode(otta.order_category_code,'ORDER',1,'MIXED',2,3), ottt.name US Order Type 13 100 1=1 ottt2.name=:line_type LOV custom Y select ottt.name value, xxen_util.meaning(otta.order_category_code,'ORDER_CATEGORY',660) description from oe_transaction_types_all otta, oe_transaction_types_tl ottt where (:$flex$.operating_unit is null or otta.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and otta.transaction_type_code='LINE' and otta.transaction_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') order by decode(otta.order_category_code,'ORDER',1,'MIXED',2,3), ottt.name US Line Type 14 110 1=1 oola.item_type_code=:item_type_code LOV Oracle OM: Item Types Y select lookup_code id, meaning value, null description from oe_lookups where lookup_type='ITEM_TYPE' order by meaning US Item Type 15 120 1=1 ooha.open_flag='Y' and oola.max_open_flag='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y select 'Y' from dual where :$flex$.order_number is null US Open only 16 130 1=1 ooha.cancelled_flag='N' and oola.cancelled_flag='N' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y select 'Y' from dual where :$flex$.order_number is null US Exclude Cancelled 17 140 1=1 ooha.flow_status_code=xxen_util.lookup_code(:header_status,'FLOW_STATUS',660) LOV ONT Order Status 8E2FF36EDF0979D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='FLOW_STATUS' and flvv.view_application_id=660 and flvv.security_group_id=0 order by flvv.meaning US Order Status 18 150 1=1 oola.flow_status_code=xxen_util.lookup_code(:line_status,'LINE_FLOW_STATUS',660) LOV ONT Line Status 8E2FF36EDF0A79D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='LINE_FLOW_STATUS' and flvv.view_application_id=660 and flvv.security_group_id=0 order by flvv.meaning US Line Status 19 160 1=1 oola.flow_status_code<>xxen_util.lookup_code(:excude_line_status,'LINE_FLOW_STATUS',660) LOV ONT Line Status 8E2FF36EDF0A79D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='LINE_FLOW_STATUS' and flvv.view_application_id=660 and flvv.security_group_id=0 order by flvv.meaning US Exclude Line Status 20 170 1=1 msiv.concatenated_segments like :item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.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) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 21 180 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_org_id_column=>'ship_from_org_id') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=1)) US Category Set 1 22 190 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_org_id_column=>'ship_from_org_id') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=2)) US Category Set 2 23 200 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_org_id_column=>'ship_from_org_id') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 24 210 1=1 oola.shippable_flag=:shippable_flag LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Shippable Flag 25 220 1=1 oola.ord_shipped_qty>0 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Order Fully/Partially Shipped 26 1=1 nvl(oola.ord_shipped_qty,0)=0 N US Order Fully/Partially Shipped 27 230 1=1 ppa.project_number=:project_number LOV PA Project Number 8E2FF36EDEDB79D2E0530100007F1FF2 Y Y select ppa.project_number value, ppa.project_type||': '||ppa.name description from ( select ppa.segment1 project_number, ppa.name, ppa.project_type, ppa.org_id, ppa.creation_date from pa_projects_all ppa union all select psm.project_number, psm.project_name, 'PJM Seiban' project_type, psm.operating_unit, psm.creation_date from pjm_seiban_numbers psm ) ppa, hr_all_organization_units_vl haouv where ( xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' or :$flex$.operating_unit is null and ppa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and ppa.org_id=haouv.organization_id order by ppa.creation_date desc, ppa.project_number desc US Project 28 240 1=1 pt.task_number=:task_number LOV PA Task Number (project dependent) 8E2FF36EDEE979D2E0530100007F1FF2 Y select pt.task_number value, pt.task_name||': '||pt.description description from pa_tasks pt, ( select ppa.project_id, ppa.segment1 project_number from pa_projects_all ppa union all select psm.project_id, psm.project_number from pjm_seiban_numbers psm ) ppa where ppa.project_number=:$flex$.project and pt.project_id=ppa.project_id order by pt.task_number US Task 29 250 1=1 oola.schedule_ship_date>=:schedule_ship_date_from Date US Schedule Ship Date From 30 260 1=1 oola.schedule_ship_date<:schedule_ship_date_to+1 Date last_day(:$flex$.schedule_ship_date_from) US Schedule Ship Date To 31 270 1=1 oola.ship_to_org_id in ( select hcsua.site_use_id from fnd_territories_vl ftv, hz_locations hl, hz_party_sites hps, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua where ftv.territory_short_name=:country and ftv.territory_code=hl.country and hl.location_id=hps.location_id and hps.party_site_id=hcasa.party_site_id and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id ) LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name select ftv.territory_short_name from fnd_territories_vl ftv where :$flex$.intrastat_eligible_only is not null and ftv.territory_code not in (select hla.country from hr_all_organization_units_vl haouv, hr_locations_all hla where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.location_id=hla.location_id) and ftv.territory_code in ('AT','BE', 'BG','CY','CZ','DE','DK','EE','ES','FI','FR','GR','HR','HU','IE','IT','LT','LU','LV','MT','NL','PL','PT','RO','SE','SI','SK') US Ship To Country 32 280 1=1 oola.invoice_to_org_id in ( select hcsua.site_use_id from fnd_territories_vl ftv, hz_locations hl, hz_party_sites hps, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua where ftv.territory_short_name=:bill_to_country and ftv.territory_code=hl.country and hl.location_id=hps.location_id and hps.party_site_id=hcasa.party_site_id and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id ) LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name US Bill To Country 33 290 1=1 oola.ship_from_org_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:ship_from_warehouse) LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Ship From Warehouse 34 300 1=1 oola.request_date>=:request_date_from Date US Request Date From 35 310 1=1 oola.request_date<:request_date_to+1 Date last_day(:$flex$.request_date_from) US Request Date To 36 320 1=1 ooha.creation_date>=:creation_date_from Date US Creation Date From 37 330 1=1 ooha.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 38 340 1=1 oola.creation_date>=:line_creation_date_from Date US Line Creation Date From 39 350 1=1 oola.creation_date<:line_creation_date_to+1 Date last_day(:$flex$.line_creation_date_from) US Line Creation Date To 40 360 1=1 oola.line_id in (select oolh.line_id from oe_order_lines_history oolh where oolh.hist_creation_date>=:cancel_date_from and oolh.hist_creation_date<:cancel_date_to+1 and oolh.hist_type_code='CANCELLATION') Date US Cancelled Date From 41 370 1=1 oola.line_id in (select oolh.line_id from oe_order_lines_history oolh where oolh.hist_creation_date<:cancel_date_to+1 and oolh.hist_creation_date>=:cancel_date_from and oolh.hist_type_code='CANCELLATION') Date last_day(:$flex$.cancelled_date_from) US Cancelled Date To 42 380 1=1 rctlgda0.gl_date>=:invoice_gl_date_from and rctla.interface_line_attribute6=oola.line_id and ooha.header_id in ( select oola_inv.header_id from ra_cust_trx_line_gl_dist_all rctlgda_inv, ra_customer_trx_lines_all rctla_inv, oe_order_lines_all oola_inv where rctlgda_inv.gl_date>=:invoice_gl_date_from and (:invoice_gl_date_to is null or rctlgda_inv.gl_date<:invoice_gl_date_to+1) and rctlgda_inv.account_class='REC' and rctlgda_inv.latest_rec_flag='Y' and rctlgda_inv.customer_trx_id=rctla_inv.customer_trx_id and rctla_inv.interface_line_context in ('INTERCOMPANY','ORDER ENTRY') and rctla_inv.interface_line_attribute6=oola_inv.line_id ) Date US Invoice GL Date From 43 390 1=1 rctlgda0.gl_date<:invoice_gl_date_to+1 and rctla.interface_line_attribute6=oola.line_id Date last_day(:$flex$.invoice_gl_date_from) US Invoice GL Date To 44 400 1=1 rcta.complete_flag='Y' and (rcta.cust_trx_type_id,rcta.org_id) in (select rctta.cust_trx_type_id, rctta.org_id from ra_cust_trx_types_all rctta where rctta.post_to_gl='Y') and rcta.customer_trx_id in (select rctlgda.customer_trx_id from ra_cust_trx_line_gl_dist_all rctlgda where rctlgda.gl_posted_date is not null) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Intrastat Eligible Only 45 410 1=1 ooha.booked_flag='Y' and ooha.cancelled_flag='N' and oola.cancelled_flag='N' and oola.shippable_flag='Y' and oola.line_category_code='ORDER' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US DIFOT Eligible Only 46 420 &dff_columns select xxen_util.dff_columns(p_table_name=>'oe_order_headers_all',p_column_name_prefix=>'Header: ',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'oe_order_lines_all',p_column_name_prefix=>'Line: ',p_display_mode=>:dff_display) sql_text from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D Y select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 47 &dff_columns2 select xxen_util.dff_columns(p_table_name=>'oe_order_headers_all',p_column_name_prefix=>'Header: ',p_prefix=>'x.',p_display_mode=>:dff_display)|| xxen_util.dff_columns(p_table_name=>'oe_order_lines_all',p_column_name_prefix=>'Line: ',p_prefix=>'x.',p_display_mode=>:dff_display) sql_text from dual US DFF Display 48 430 &o2c_columns (select min(apsa.due_date) from ar_payment_schedules_all apsa where x.customer_trx_id=apsa.customer_trx_id) due_date, case when row_number() over (partition by x.customer_trx_id order by x.header_id, x.line_id, wnd.name nulls last)=1 then (select sum(apsa.amount_due_remaining) from ar_payment_schedules_all apsa where x.customer_trx_id=apsa.customer_trx_id) end amount_due_remaining, (select max(apsa.actual_date_closed) from ar_payment_schedules_all apsa where x.customer_trx_id=apsa.customer_trx_id having min(apsa.status)='CL') paid_date, ( select max(acra.receipt_number) keep (dense_rank last order by araa.apply_date, araa.receivable_application_id) from ar_receivable_applications_all araa, ar_cash_receipts_all acra where x.customer_trx_id=araa.applied_customer_trx_id and araa.status='APP' and araa.display='Y' and araa.cash_receipt_id=acra.cash_receipt_id ) receipt_number, ( select xxen_util.client_time(max(apc.creation_date)) from ra_cust_trx_line_gl_dist_all rctlgda, ar_posting_control apc where x.customer_trx_id=rctlgda.customer_trx_id and rctlgda.posting_control_id=apc.posting_control_id ) accounted_date, ( select xxen_util.client_time(max(gjh.posted_date)) from gl_import_references gir, gl_je_headers gjh where to_char(x.customer_trx_id)=gir.reference_2 and gir.reference_6='CUSTOMER' and gir.reference_10='RA_CUST_TRX_LINE_GL_DIST' and gir.je_header_id=gjh.je_header_id and gjh.status='P' ) gl_posted_date, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show Order to Cash Details Adds the booked date and open holds of each line, and the due date, amount due, paid date, latest receipt, accounted date and GL posted date of its invoice. 49 &o2c_columns_x xxen_util.client_time(ooha.booked_date) booked_date, max(rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) customer_trx_id, Y US Show Order to Cash Details 50 &o2c_order_columns x.booked_date, ( select listagg(ohd.name,', ') within group (order by ohd.name) from oe_order_holds_all oohoa, oe_hold_sources_all ohsa, oe_hold_definitions ohd where x.header_id=oohoa.header_id and (oohoa.line_id is null or oohoa.line_id=x.line_id) and oohoa.released_flag='N' and oohoa.hold_source_id=ohsa.hold_source_id and ohsa.hold_id=ohd.hold_id ) hold_name, (select xxen_util.client_time(min(oohoa.creation_date)) from oe_order_holds_all oohoa where x.header_id=oohoa.header_id and (oohoa.line_id is null or oohoa.line_id=x.line_id) and oohoa.released_flag='N') hold_date, Y US Show Order to Cash Details 51 440 1=1 ooha.header_id in (select oola_b.header_id from oe_order_lines_all oola_b where oola_b.blanket_number=:sales_agreement_number) and oola.blanket_number=:sales_agreement_number LOV custom Y select obha.order_number value, obha.sales_document_name||' '||hca.account_name||' ('||xxen_util.meaning(obha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_blanket_headers_all obha, hz_cust_accounts hca where (:$flex$.operating_unit is null or obha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and obha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and obha.sold_to_org_id=hca.cust_account_id(+) order by obha.order_number desc US Sales Agreement Number Order lines released against this blanket sales agreement 52 450 &osc_columns osc.salesperson credit_salesperson, osc.sales_group credit_sales_group, osc.sales_credit_type, xxen_util.yes(osc.quota_flag) quota, xxen_util.yes(osc.header_credit) header_sales_credit, osc.percent credit_percent, case when row_number() over (partition by x.line_id, osc.sales_credit_id order by wnd.name nulls last)=1 then x.extended_price*osc.percent/100 end credited_amount, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show Sales Credits Adds one row per sales credit of each line. Lines without sales credits of their own show the order header sales credits. 53 &osc_from , ( select /*+ push_pred(osc) */ oola.line_id, osc.sales_credit_id, osc.percent, nvl2(osc.line_id,null,'Y') header_credit, osct.name sales_credit_type, osct.quota_flag, (select jrrev.resource_name from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where osc.salesrep_id=jrs.salesrep_id and oola.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id) salesperson, (select jrgv.group_name from jtf_rs_groups_vl jrgv where osc.sales_group_id=jrgv.group_id) sales_group from oe_order_lines_all oola, ( select osc.line_id credit_line_id, osc.* from oe_sales_credits osc where osc.line_id is not null union all select oola2.line_id credit_line_id, osc.* from oe_order_lines_all oola2, oe_sales_credits osc where oola2.header_id=osc.header_id and osc.line_id is null and oola2.line_id not in (select osc2.line_id from oe_sales_credits osc2 where osc2.line_id is not null) ) osc, oe_sales_credit_types osct where oola.line_id=osc.credit_line_id and osc.sales_credit_type_id=osct.sales_credit_type_id(+) ) osc Y US Show Sales Credits 54 &osc_where and x.line_id=osc.line_id(+) Y US Show Sales Credits CAAE88BC4CAF4B938D0AB37E1C1AD0E5 Default Y SYSADMIN 91 ACTUAL_SHIPMENT_DATE 20 BILL_TO_ADDRESS 21 BILL_TO_COUNTRY 17 BILL_TO_CUSTOMER 18 BILL_TO_CUSTOMER_NUMBER 19 BILL_TO_LOCATION 22 BILL_TO_TAX_REFERENCE 83 CALCULATE_PRICE_FLAG 64 CANCELLED_AMOUNT 61 CANCELLED_BY 59 CANCELLED_FLAG 63 CANCELLED_QUANTITY 60 CANCEL_DATE 62 CANCEL_REASON 33 CHARGES 30 CURRENCY 2 CUSTOMER 3 CUSTOMER_NUMBER 10 CUSTOMER_PO 69 Class 72 Commodity 96 DELIVERY 66 DESCRIPTION 76 DISCOUNTED_PRICE 79 EXTENDED_PRICE 50 FOB 49 FREIGHT_TERMS 68 Family 104 HEADER_CREATED_BY 105 HEADER_CREATION_DATE 114 HEADER_ID 106 HEADER_LAST_UPDATED_BY 107 HEADER_LAST_UPDATE_DATE 29 HEADER_STATUS 93 INVOICED_QUANTITY 45 INVOICE_ACCOUNTED_AMOUNT 44 INVOICE_AMOUNT 36 INVOICE_CLASS 42 INVOICE_CURRENCY 39 INVOICE_DATE 40 INVOICE_GL_DATE 43 INVOICE_LINE 38 INVOICE_NUMBER 26 INVOICE_SALESPERSON 41 INVOICE_STATUS 37 INVOICE_TYPE 65 ITEM 67 ITEM_TYPE 70 Inv.Items Description 71 Item Category 55 LINE 113 LINE_CATEGORY 80 LINE_CHARGES 108 LINE_CREATED_BY 109 LINE_CREATION_DATE 117 LINE_DAYS_LATE 128 LINE_DIF 130 LINE_DIFOT 129 LINE_DOT 115 LINE_ID 110 LINE_LAST_UPDATED_BY 111 LINE_LAST_UPDATE_DATE 116 LINE_NUMBER 118 LINE_QUANTITY_SHORT 48 LINE_SET 119 LINE_SHIPPED 58 LINE_STATUS 57 LINE_TYPE 75 LIST_PRICE 101 Line: DFF Context 103 Line: Installation End Date 102 Line: Installation Start Date 97 NEXT_STEP 1 OPERATING_UNIT 23 ORDERED_DATE 131 ORDERS_DIF 133 ORDERS_DIFOT 132 ORDERS_DOT 112 ORDER_CATEGORY 120 ORDER_DATE_TYPE_CODE 4 ORDER_NUMBER 27 ORDER_SOURCE 28 ORDER_SOURCE_REFERENCE 9 ORDER_TYPE 126 ORIG_LINE_ACTUAL_SHIPMENT_DATE 125 ORIG_LINE_DELIVERY_LEAD_TIME 122 ORIG_LINE_ID 123 ORIG_LINE_PROMISE_DATE 124 ORIG_LINE_QUANTITY 127 ORIG_LINE_TOT_SHIPPED_QUANTITY 90 OVERRIDE_ATP_DATE_CODE 53 PACKING_INSTRUCTIONS 35 PAYMENT_TERMS 54 PAYMENT_TYPE 24 PRICE_LIST 86 PRICING_DATE 84 PRICING_QUANTITY 85 PRICING_UOM 99 PROJECT 88 PROMISE_DATE 73 Purchasing Description 78 QUANTITY 5 QUOTE_NUMBER Y 98 RELEASE_STATUS 87 REQUEST_DATE 25 SALESPERSON 89 SCHEDULE_SHIP_DATE 56 SHIPMENT_NUMBER 51 SHIPMENT_PRIORITY 94 SHIPPABLE_FLAG 92 SHIPPED_QUANTITY 52 SHIPPING_INSTRUCTIONS 47 SHIPPING_METHOD 95 SHIP_SET 14 SHIP_TO_ADDRESS 15 SHIP_TO_COUNTRY 11 SHIP_TO_CUSTOMER 12 SHIP_TO_CUSTOMER_NUMBER 13 SHIP_TO_LOCATION 16 SHIP_TO_TAX_REFERENCE 7 SOURCE_DOCUMENT 6 SOURCE_TYPE 121 SPLIT_LINE 31 SUBTOTAL 100 TASK 32 TAX 81 TAX_AMOUNT 82 TAX_CODE 34 TOTAL 8 TYPE 77 UNIT_SELLING_PRICE 74 UOM 46 WAREHOUSE S Site 1053132AE2404958A2D3F0E988282290 Intrastat Y ENGINATICS ACCOUNT_NUMBER ACTUAL_SHIPMENT_DATE BILL_TO_ACCOUNT 11 BILL_TO_ADDRESS 12 BILL_TO_COUNTRY 10 BILL_TO_CUSTOMER 9 BILL_TO_CUSTOMER_NUMBER BILL_TO_LOCATION 13 BILL_TO_TAX_REFERENCE CALCULATE_PRICE_FLAG CANCELLED_AMOUNT CANCELLED_BY CANCELLED_FLAG CANCELLED_QUANTITY CANCEL_DATE CANCEL_REASON CHARGES CURRENCY 8 CUSTOMER 7 CUSTOMER_NUMBER CUSTOMER_PO Class -22.0024 Commodity DELIVERY 21 DESCRIPTION DISCOUNTED_PRICE EXTENDED_PRICE 31 FOB 30 FREIGHT_TERMS Family HEADER_CREATED_BY HEADER_CREATION_DATE HEADER_ID HEADER_LAST_UPDATED_BY HEADER_LAST_UPDATE_DATE HEADER_STATUS -22.0023 INVOICED_AMOUNT 28 INVOICE_ACCOUNTED_AMOUNT 26 INVOICE_AMOUNT 1 INVOICE_CLASS 27 INVOICE_CURRENCY 4 INVOICE_DATE Y 5 INVOICE_GL_DATE INVOICE_LINE 3 INVOICE_NUMBER INVOICE_SALESPERSON INVOICE_STATUS 2 INVOICE_TYPE 20 ITEM ITEM_TYPE -17.0018 Inv.Items Description -22.0023 Item Category 19 LINE LINE_CATEGORY LINE_CHARGES LINE_CREATED_BY LINE_CREATION_DATE LINE_DAYS_LATE LINE_DIF LINE_DIFOT LINE_DOT LINE_ID LINE_LAST_UPDATED_BY LINE_LAST_UPDATE_DATE LINE_NUMBER LINE_QUANTITY_SHORT LINE_SET LINE_SHIPPED LINE_STATUS LINE_TYPE LIST_PRICE 23 Line: DFF Context 25 Line: Installation End Date 24 Line: Installation Start Date NEXT_STEP OPERATING_UNIT ORDERED_DATE ORDERS_DIF ORDERS_DIFOT ORDERS_DOT ORDER_CATEGORY ORDER_DATE_TYPE_CODE 6 ORDER_NUMBER ORDER_SOURCE ORDER_SOURCE_REFERENCE ORDER_TYPE ORIG_LINE_ACTUAL_SHIPMENT_DATE ORIG_LINE_DELIVERY_LEAD_TIME ORIG_LINE_ID ORIG_LINE_PROMISE_DATE ORIG_LINE_QUANTITY ORIG_LINE_TOT_SHIPPED_QUANTITY PACKING_INSTRUCTIONS PAYMENT_TERMS PAYMENT_TYPE PRICE_LIST PRICING_DATE PRICING_QUANTITY PRICING_UOM PROJECT PROMISE_DATE Product Cat Product Description -22.0025 Purchasing Description QUANTITY QUOTE_NUMBER REQUEST_DATE SALESPERSON SALES_AGREEMENT_LINE SALES_AGREEMENT_NUMBER SCHEDULE_SHIP_DATE SHIPMENT_PRIORITY SHIPPABLE_FLAG 22 SHIPPED_QUANTITY SHIPPING_INSTRUCTIONS SHIPPING_METHOD SHIP_SET SHIP_TO_ACCOUNT 16 SHIP_TO_ADDRESS 17 SHIP_TO_COUNTRY 15 SHIP_TO_CUSTOMER 14 SHIP_TO_CUSTOMER_NUMBER SHIP_TO_LOCATION 18 SHIP_TO_TAX_REFERENCE SOURCE_DOCUMENT SOURCE_TYPE SPLIT_LINE SUBTOTAL TASK TAX TAX_AMOUNT 29 TAX_CODE TOTAL TYPE UNIT_SELLING_PRICE UOM WAREHOUSE Category Set 1 Category Set 2 Intrastat Eligible Only Y Open only Operating Unit Vision Operations Shippable Flag Y S Site Account Number Bill To Country Cancelled Date From Cancelled Date To Category Set 1 Category Set 2 Creation Date From Creation Date To Customer Name DIFOT Eligible Only Item Line Category Line Creation Date From Line Creation Date To Line Status Line Type Open only Order Category Order Fully/Partially Shipped Order Number Order Status Order Type Project Quote Number Request Date From Request Date To Schedule Ship Date From Schedule Ship Date To Ship From Warehouse Task Type 3586B413E015549E9A9BC26E77BDA29E Open Order Backlog by Salesperson Open sales order lines credited to each salesperson, with the credited backlog amount per salesperson and currency. ENGINATICS 13 CREDITED_AMOUNT 12 CREDIT_PERCENT 9 CREDIT_SALESPERSON 2 14 CURRENCY 4 CUSTOMER 7 DESCRIPTION 6 ITEM 5 LINE 4 8 LINE_STATUS 1 OPERATING_UNIT 1 3 ORDERED_DATE 2 ORDER_NUMBER 3 11 QUOTA 16 REQUEST_DATE 10 SALES_CREDIT_TYPE 15 SCHEDULE_SHIP_DATE CREDITED_AMOUNT VALUE 1 SUM CREDIT_SALESPERSON ROW 1 CURRENCY COLUMN 1 Exclude Cancelled Y Open only Y Show Sales Credits Y S Site F201C916EFF352E482F982C4DAB6A93F Sales Agreement Releases Sales order lines released against a blanket sales agreement. Enter the Sales Agreement Number to list the releases of one agreement. ENGINATICS 18 ACTUAL_SHIPMENT_DATE 15 CURRENCY 6 CUSTOMER 7 CUSTOMER_NUMBER 10 DESCRIPTION 14 EXTENDED_PRICE 9 ITEM 8 LINE 4 16 LINE_STATUS 1 OPERATING_UNIT 5 ORDERED_DATE 4 ORDER_NUMBER 3 12 QUANTITY 3 SALES_AGREEMENT_LINE 2 2 SALES_AGREEMENT_NUMBER 1 17 SCHEDULE_SHIP_DATE 13 UNIT_SELLING_PRICE 11 UOM S Site 910827C7FA38539E9AF58890D4890E77 Sales Credits by Salesperson Order line sales credits per salesperson, including split and non-quota credits, with the credited amount summed by salesperson and credit type. ENGINATICS 13 CREDITED_AMOUNT 12 CREDIT_PERCENT 9 CREDIT_SALESPERSON 2 14 CURRENCY 4 CUSTOMER 7 DESCRIPTION 6 ITEM 5 LINE 4 8 LINE_STATUS 1 OPERATING_UNIT 1 3 ORDERED_DATE 2 ORDER_NUMBER 3 11 QUOTA 10 SALES_CREDIT_TYPE CREDITED_AMOUNT VALUE 1 SUM CREDIT_SALESPERSON ROW 1 CURRENCY COLUMN 1 SALES_CREDIT_TYPE ROW 2 Exclude Cancelled Y Open only N Show Sales Credits Y S Site CAAE88BC4CAF4B938D0AB37E1C1AD0E5