CAC Order Type Setup

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to display the sales order transaction types with the corresponding receivables (A/R) transaction types.

/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use ...  Report to display the sales order transaction types with the corresponding receivables (A/R) transaction types.

/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_oe_transaction_types.sql
-- |
-- | Parameters:
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- | Description:
-- | Report to display the sales order transaction types with the corresponding
-- | receivables (A/R) transaction types.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 15 Nov 2016 Douglas Volz Initial Coding
-- | 1.1 09 Jan 2017 Douglas Volz Added description and Receivables Txn Type
-- | 1.2 11 Jan 2017 Douglas Volz Added Order Category, Order Type (DFF),
-- | and Disti Plus Pricing (DFF), and add a
-- | new section for Order Types with no COGS
-- | account.
-- | 1.3 14 Mar 2017 Douglas Volz Changed apps.ra_cust_trx_types to
-- | apps.ra_cust_trx_types_all, added A/R
-- | revenue account
-- | 1.4 23 Aug 2017 Douglas Volz Add user-defined field (descriptive flex-
-- | field) for the COGS ICP account, a contra-
-- | account which is used to record the ICP
-- | portion of the COGS entry.
-- | 1.5 13 Mar 2019 Douglas Volz Added Operating Unit Parameter.
-- | 1.6 22 May 2019 Douglas Volz Added missing ORG_ID join
-- | 1.7 14 Apr 2020 Douglas Volz Added creation date to report
-- | 1.8 16 Apr 2020 Douglas Volz Moved sales ccid joins to outer query
-- | and added Ledger parameter.
-- | 1.9 28 Apr 2020 Douglas Volz Changed to multi-language views for the
-- | inventory orgs and operating units.
-- +=============================================================================+*/   more

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select nvl(gl.short_name, gl.name) Ledger,
 haou.name Operating_Unit,
 ottt.name Order_Type_Name,
 otta.transaction_type_id Order_Type_Id,
 otta.transaction_type_code Transaction_Type_Code,
 otta.order_category_code Order_Category,
 otta.currency_code Currency_Code,
 otta.start_date_active Start_Date,
 otta.end_date_active End_Date,
 rctt.name AR_Transaction_Name,
 rctt.description AR_Transaction_Description,
 rctt.type AR_Transaction_Type,
 &segment_columns
 -- Revision for version 1.8
 &segment_columns2
 -- Revision for version 1.7
 otta.creation_date Creation_Date,
 otta.last_update_date Last_Update_Date
from oe_transaction_types_tl ottt,
 oe_transaction_types_all otta,
 gl_code_combinations gcc1, -- COGS_Accounts
 -- Revision for version 1.8
 gl_code_combinations gcc2, -- Sales_Accounts
 -- Revision for version 1.3
 -- Revision for version 1.1
 -- ra_cust_trx_types rctt,
 (select  rctt.cust_trx_type_id,
  rctt.org_id,
  rctt.name,
  rctt.description,
  rctt.type,
  rctt.gl_id_rev
  from ra_cust_trx_types_all rctt
 ) rctt,
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,  -- operating unit
 gl_ledgers gl
where   ottt.transaction_type_id    = otta.transaction_type_id
and ottt.language               = userenv('lang')
and gcc1.code_combination_id (+)= otta.cost_of_goods_sold_account
-- Revision for version 1.8
and gcc2.code_combination_id (+)= rctt.gl_id_rev
-- Revision for version 1.2
and otta.cust_trx_type_id       = rctt.cust_trx_type_id (+)
and otta.transaction_type_code <> 'LINE'
-- Revision for version 1.6
and otta.org_id                 = rctt.org_id
-- Revision for version 1.7
and hoi.org_information_context = 'Operating Unit Information'
and hoi.organization_id         = haou.organization_id -- this gets the operating unit id
and gl.ledger_id                = to_number(hoi.org_information3) -- this joins OU to GL
-- End revision for version 1.7 
and haou.organization_id        = otta.org_id -- this gets the operating unit id
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou.organization_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 1=1                         -- p_operating_unit, p_ledger
order by 1,2,3,4,5,7
Parameter Name SQL text Validation
Operating Unit 
haou.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
   
Related Blitz Reports
CAC Open Internal ISO-IRO CAC Deferred COGS Out-of-Balance CAC Material Account Detail CAC ICP PII Material Account Detail CAC Recost Cost of Goods Sold CAC Open Purchase Orders CAC Internal Order Shipment Margin CAC Manufacturing Variance Summary CAC ICP PII Inventory Pending Cost Adjustment
Blitz Report™

Blitz Report™ provides multiple benefits: