Skip to Main Content

E-Business Suite

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to find/list seeded concurrent programs for SALES report in EBS R12.2

Beauty_and_dBestJun 18 2019 — edited Feb 9 2021

EBS R12.2

12c

OL6

Hi ALL,

We have these custom SALES ANALYSIS report which accessed all the tables below:

FND_USER USR

'RA_CUSTOMER_TRX_ALL

'RA_CUSTOMER_TRX_LINES_ALL

RA_CUST_TRX_TYPES_ALL

RA_CUSTOMERS

RA_SALESREPS_ALL

TF_RS_SALESREPS

RA_TERMS

HZ_CUST_SITE_USES_ALL

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITES HZ_PRTY

HZ_LOCATIONS

RA_BATCH_SOURCES_ALL

RA_TERMS_LINES

GL_CODE_COMBINATIONS

FND_FLEX_VALUES_TL

FND_FLEX_VALUES_TL

AR_PAYMENT_SCHEDULES_ALL

RA_CUST_TRX_LINE_GL_DIST_ALL

FND_FLEX_VALUES_TL

FND_FLEX_VALUES

RA_ADDRESSES_ALL

The report name is

SALES ANALYSISinv date from: '01-MAY-2019', inv date to: '31-MAY-2019'As of  :  10-JUN-19

The report query is below:


--AR INVOICE_X > Invoice Lines
-- INVOICE LINE AMOUNTS --- 
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, INVL.INVENTORY_ITEM_ID ITEM_CODE
, MTLS.DESCRIPTION ITEM_DESCRIPTION
  ,mtls.segment1 item_code2
, 'LINE' INV_LINE_TYPE
-- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
, MTLS.DESCRIPTION  INV_LINE_DESC
, INVL.QUANTITY_INVOICED INV_QTY
, INVL.UOM_CODE UOM
, (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1
,MTLS.UNIT_WEIGHT  physicalwt
  ,MTLS.WEIGHT_UOM_CODE physicaluom
, INVL.UNIT_SELLING_PRICE UNIT_SP
, INVL.EXTENDED_AMOUNT
, INVL.EXTENDED_AMOUNT LINE_AMOUNT
, 0 LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
,vats.printed_tax_name    taxcode
--ADDED
,itemast.inv_type   TYPES1
,itemast.seq_dpnd_class   classes1
,itemast.ship_class GROUPname1
,itemast.attribute11 att11
,itmsubt.tech_class_desc techd
,itemast.attribute10 pellet_crum_mash
,whsem.whse_code  warehousen
,invl.INTERFACE_line_attribute3 OMDR
--ADDED ABOVE
from           ic_whse_mst  whsem,
RA_CUSTOMER_TRX_LINES_ALL INVL
, MTL_SYSTEM_ITEMS_B MTLS
,ar_vat_tax_all_tl  vats
--ADDED
,gmi_item_categories  itmcat
,tech_cls_subcls_vw     itmsubt
,ic_item_mst     itemast
--ADD ABOVE
where invl.interface_line_attribute10=whsem.mtl_organization_id(+)  and
INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)
AND MTLS.ORGANIZATION_ID=106
--ADDED
AND MTLS.SEGMENT1= ITEMAST.ITEM_NO
AND itemast.item_id=itmcat.item_id AND
itmcat.CATEGORY_SET_ID=1100000047 AND itmcat.category_id=itmsubt.tech_category_id
--ADDED ABOVE
and INVL.EXTENDED_AMOUNT > 0
and INVL.LINE_TYPE = 'LINE'   and invl.quantity_credited is null
--and  invl.quantity_ordered is not null
and invl.vat_tax_id=vats.vat_tax_id(+)
UNION
-- INVOICE LINE DISCOUNTS --- 
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, INVL.INVENTORY_ITEM_ID ITEM_CODE
, MTLS.DESCRIPTION ITEM_DESCRIPTION
,mtls.segment1    item_code2
, 'DISCOUNT' INV_LINE_TYPE
--, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
, MTLS.DESCRIPTION  INV_LINE_DESC
, 0 INV_QTY
, INVL.UOM_CODE UOM
, 0 AT50_KG1
,MTLS.UNIT_WEIGHT  physicalwt
  ,MTLS.WEIGHT_UOM_CODE physicaluom
, 0 UNIT_SP
, INVL.EXTENDED_AMOUNT
, 0 LINE_AMOUNT
, INVL.EXTENDED_AMOUNT LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, 0 ALT_QTY
, ORDA.PRICING_GROUP_SEQUENCE DISC_SEQ
, ORDA.LIST_LINE_NO DISC_DESC
, DECODE(ORDA.ARITHMETIC_OPERATOR, 'AMT', ' P'|| ORDA.OPERAND, ORDA.OPERAND ||'%') DISC_CODE
,' '  taxcode
--ADDED
,itemast.inv_type   TYPES1
,itemast.seq_dpnd_class   classes1
,itemast.ship_class GROUPname1
,itemast.attribute11 att11
,itmsubt.tech_class_desc techd
--ADDED ABOVE
,itemast.attribute10 pellet_crum_mash
,whsem.whse_code  warehousen
,invl.INTERFACE_line_attribute3 OMDR
from           ic_whse_mst  whsem,
RA_CUSTOMER_TRX_LINES_ALL INVL
, OE_PRICE_ADJUSTMENTS ORDA
, MTL_SYSTEM_ITEMS_B MTLS
--ADDED
,gmi_item_categories  itmcat
,tech_cls_subcls_vw     itmsubt
,ic_item_mst     itemast
--ADD ABOVE
where           invl.interface_line_attribute10=whsem.mtl_organization_id(+)  and
  INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)
AND MTLS.ORGANIZATION_ID=106
--ADDED
AND MTLS.SEGMENT1= ITEMAST.ITEM_NO
AND itemast.item_id=itmcat.item_id AND
itmcat.CATEGORY_SET_ID=1100000047 AND itmcat.category_id=itmsubt.tech_category_id
--ADDED ABOVE
and INVL.EXTENDED_AMOUNT < 0   and invl.quantity_credited  is null
and INVL.LINE_TYPE = 'LINE'
and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+)
and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+)
UNION
-- FREIGHT CHARGES --- 
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, INVL.INVENTORY_ITEM_ID ITEM_CODE
, MTLS.DESCRIPTION ITEM_DESCRIPTION
,mtls.segment1    item_code2
, 'FREIGHT' INV_LINE_TYPE
--, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
, MTLS.DESCRIPTION  INV_LINE_DESC
, 0 INV_QTY
,' ' UOM
, 0 AT50_KG1
,MTLS.UNIT_WEIGHT  physicalwt
,MTLS.WEIGHT_UOM_CODE physicaluom
, 0 UNIT_SP
, INVL.EXTENDED_AMOUNT
, 0 LINE_AMOUNT
, 0 LINE_DISCOUNT
, INVL.EXTENDED_AMOUNT FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
,' ' taxcode
,' '  TYPES1
,' ' classes1
,' ' GROUPname1
,' ' att11
,' '   techd
,' ' pellet_crum_mash
,' ' warehousen
,' '  OMDR
from
RA_CUSTOMER_TRX_LINES_ALL INVL
, MTL_SYSTEM_ITEMS_B MTLS
where
INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)
and INVL.LINE_TYPE = 'FREIGHT'
UNION
-- TAXES --- 
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, INVL.INVENTORY_ITEM_ID ITEM_CODE
, MTLS.DESCRIPTION ITEM_DESCRIPTION
,mtls.segment1    item_code2
, 'TAX' INV_LINE_TYPE
-- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
, MTLS.DESCRIPTION  INV_LINE_DESC
, 0 INV_QTY
, INVL.UOM_CODE UOM
, 0 AT50_KG1
,MTLS.UNIT_WEIGHT  physicalwt
  ,MTLS.WEIGHT_UOM_CODE physicaluom
, INVL.UNIT_SELLING_PRICE UNIT_SP
, INVL.EXTENDED_AMOUNT
, 0 LINE_AMOUNT
, 0 LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, INVL.EXTENDED_AMOUNT TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
, ' ' taxcode
--ADDED
,' '   TYPES1
,' '  classes1
,' ' GROUPname1
,' ' att11
,' '   techd
,' ' pellet_crum_mash
,' '  warehousen
,' ' OMDR
from
RA_CUSTOMER_TRX_LINES_ALL INVL
, MTL_SYSTEM_ITEMS_B MTLS
where
INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)
and INVL.LINE_TYPE = 'TAX'
union 
--DM
-- INVOICE LINE AMOUNTS --- 
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, 0 ITEM_CODE
, '  '  ITEM_DESCRIPTION
,' '   item_code2
, 'LINE' INV_LINE_TYPE
, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
--    , MTLS.DESCRIPTION  INV_LINE_DESC
, INVL.QUANTITY_INVOICED INV_QTY
, INVL.UOM_CODE UOM
--, (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1
,0  AT50_KG1
,0  physicalwt
,'  ' physicaluom
, INVL.UNIT_SELLING_PRICE UNIT_SP
, INVL.EXTENDED_AMOUNT
, INVL.EXTENDED_AMOUNT LINE_AMOUNT
, 0 LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
  ,vats.printed_tax_name taxcode
--ADDED
,' '   TYPES1
,' '   classes1
,' ' GROUPname1
,' 'att11
,' '   techd
,' ' pellet_crum_mash
,' ' warehousen
,' ' OMDR
from
RA_CUSTOMER_TRX_LINES_ALL INVL
,ar_vat_tax_all_tl  vats
where 
INVL.EXTENDED_AMOUNT > 0
and INVL.LINE_TYPE = 'LINE' and  invl.quantity_ordered is  null
and invl.vat_tax_id=vats.vat_tax_id(+)
--  cm ORDER
union
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, INVL.INVENTORY_ITEM_ID ITEM_CODE
, MTLS.DESCRIPTION ITEM_DESCRIPTION
  ,mtls.segment1 item_code2
, 'LINE' INV_LINE_TYPE
-- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
, MTLS.DESCRIPTION  INV_LINE_DESC
--, INVL.QUANTITY_INVOICED INV_QTY
, INVL.QUANTITY_CREDITED  INV_QTY
, INVL.UOM_CODE UOM
, (INVL.QUANTITY_CREDITED * MTLS.UNIT_WEIGHT)/50 AT50_KG1
--         ,0 AT50_KG1
  ,MTLS.UNIT_WEIGHT physicalwt
  ,MTLS.WEIGHT_UOM_CODE physicaluom
, INVL.UNIT_SELLING_PRICE UNIT_SP
, INVL.EXTENDED_AMOUNT
, INVL.EXTENDED_AMOUNT LINE_AMOUNT
, 0 LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
,'  ' taxcode
--ADDED
,' '   TYPES1
,' '   classes1
,' ' GROUPname1
,' 'att11
,' '   techd
,' ' pellet_crum_mash
,whsem.whse_code  warehousen
,invl.INTERFACE_line_attribute3 OMDR
from             ic_whse_mst  whsem, apps.ra_customer_trx_all invh
,apps.RA_CUSTOMER_TRX_LINES_ALL INVL
  ,apps.RA_CUST_TRX_TYPES_ALL ttype
, apps.OE_PRICE_ADJUSTMENTS ORDA
, apps.MTL_SYSTEM_ITEMS_B MTLS
where invl.interface_line_attribute10=whsem.mtl_organization_id(+)  and
INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)  and
INVL.EXTENDED_AMOUNT < 0 
and INVL.LINE_TYPE = 'LINE'      
and  invh.CUSTOMER_TRX_ID = invl.CUSTOMER_TRX_ID
and invh.CUST_TRX_TYPE_ID=ttype.CUST_TRX_TYPE_ID
and ttype.type='CM'     AND INVL.SALES_ORDER_SOURCE ='ORDER ENTRY'
--xx   and invl.quantity_credited  is   null
and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+)
and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+)
--  cm MANUAL
union
select
INVL.CUSTOMER_TRX_ID
, INVL.CUSTOMER_TRX_LINE_ID
, INVL.LINE_NUMBER
, INVL.SALES_ORDER_LINE SO_LINE
, 0 ITEM_CODE
, '  '  ITEM_DESCRIPTION
,' '   item_code2
, 'LINE' INV_LINE_TYPE
, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC
--, INVL.QUANTITY_INVOICED INV_QTY
, INVL.QUANTITY_CREDITED  INV_QTY
, INVL.UOM_CODE UOM
--, (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1
,0  AT50_KG1
,0  physicalwt
,'  ' physicaluom
, INVL.UNIT_SELLING_PRICE UNIT_SP
, INVL.EXTENDED_AMOUNT
, INVL.EXTENDED_AMOUNT LINE_AMOUNT
, 0 LINE_DISCOUNT
, 0 FREIGHT_AMOUNT
, 0 TAX_AMOUNT
, INVL.LINE_TYPE
, INVL.ATTRIBUTE1 DR_NO
, INVL.ATTRIBUTE2 ALT_UOM
, TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY
, 0 DISC_SEQ
, NULL DISC_DESC
, NULL DISC_CODE
,'  ' taxcode
--ADDED
,' '   TYPES1
,' '   classes1
,' ' GROUPname1
,' 'att11
,' '   techd
, ' '  pellet_crum_mash
,' ' warehousen
, ' ' OMDR
from apps.ra_customer_trx_all invh
,apps.RA_CUSTOMER_TRX_LINES_ALL INVL
  ,apps.RA_CUST_TRX_TYPES_ALL ttype
-- , apps.OE_PRICE_ADJUSTMENTS ORDA
--, apps.MTL_SYSTEM_ITEMS_B MTLS
where
--INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+)  and
INVL.EXTENDED_AMOUNT < 0 
and INVL.LINE_TYPE = 'LINE'      
and  invh.CUSTOMER_TRX_ID = invl.CUSTOMER_TRX_ID
and invh.CUST_TRX_TYPE_ID=ttype.CUST_TRX_TYPE_ID
and ttype.type='CM'   AND INVL.SALES_ORDER_SOURCE IS NULL
--xx   and invl.quantity_credited  is   null
--      and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+)
--     and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+)   and invl.

My question is,  where do I find all other list of seeded concurrent programs (or programs pre-created by oracle)  related to SALES REPORT?

My worry is that, the custom program is too long,

- chances are it might have redundant logic? or

- not well "tuned" code, or

- there might be a shorter way to code it? or 

- there might be existing pre-built code that is same/similar output?

Please help...

Kind regards,

jc

This post has been answered by hal9000akilroy on Jun 18 2019
Jump to Answer
Comments
Post Details
Added on Jun 18 2019
2 comments
524 views