eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: FII_AR_PROD_REV_FCV

Product:	FII - Financial Intelligence (Obsolete)
Description:	FII_AR_PROD_REV_FCV is an internal view used to support Product Revenue Portlet.
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT /*+ USE_NL(GCC SOB CTL CT) */ 'NON-GEMMS' VIEW_TYPE
, SOB.CURRENCY_CODE BASE_CURRENCY
, SOB.SET_OF_BOOKS_ID||'-'|| LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE(CTLGD.ORG_ID
, NULL
, 'NA_EDW'
, CTLGD.ORG_ID||'-'|| LI.INSTANCE_CODE) OU_ORG_FK
, DECODE(CT.BILL_TO_SITE_USE_ID
, NULL
, 'NA_EDW'
, CT.BILL_TO_SITE_USE_ID||'-'|| LI.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_CUSTOMER_FK
, DECODE(GCC.COMPANY_COST_CENTER_ORG_ID
, NULL
, 'NA_EDW'
, GCC.COMPANY_COST_CENTER_ORG_ID||'-'|| LI.INSTANCE_CODE) COMPANY_CC_ORG_FK
, TRUNC(CTLGD.GL_DATE) GL_DATE
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, LI.INSTANCE_CODE INSTANCE_FK
, NVL(TO_CHAR(CTL.INVENTORY_ITEM_ID)
, 'NA_EDW') ITEM_FK
, TRUNC(CT.TRX_DATE) INVOICE_DATE
, CTLGD.ACCTD_AMOUNT AMT_B
, CTLGD.GL_DATE GL_DATE_FILTER
FROM EDW_LOCAL_INSTANCE LI
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, GL_CODE_COMBINATIONS GCC
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
WHERE CTLGD.ACCOUNT_SET_FLAG = 'N'
AND NVL(CTLGD.AMOUNT
, 0) <> 0
AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND (NVL(CTL.INTERFACE_LINE_CONTEXT
, 'XXX') NOT IN ('ORDER ENTRY'
, 'GEMMS OP') OR (CTL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND TRANSLATE(CTL.INTERFACE_LINE_ATTRIBUTE6
, 'Z0123456789'
, 'Z') IS NOT NULL) )
AND CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CTLGD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID UNION ALL SELECT 'NON-GEMMS' VIEW_TYPE
, SOB.CURRENCY_CODE BASE_CURRENCY
, SOB.SET_OF_BOOKS_ID||'-'|| LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE(ADJ.ORG_ID
, NULL
, 'NA_EDW'
, ADJ.ORG_ID||'-'|| LI.INSTANCE_CODE) OU_ORG_FK
, DECODE(CT.BILL_TO_SITE_USE_ID
, NULL
, 'NA_EDW'
, CT.BILL_TO_SITE_USE_ID||'-'|| LI.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_CUSTOMER_FK
, DECODE(GCC.COMPANY_COST_CENTER_ORG_ID
, NULL
, 'NA_EDW'
, GCC.COMPANY_COST_CENTER_ORG_ID||'-'||LI.INSTANCE_CODE) COMPANY_CC_ORG_FK
, TRUNC(ADJ.GL_DATE) GL_DATE
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, LI.INSTANCE_CODE INSTANCE_FK
, 'NA_EDW' ITEM_FK
, TRUNC(ADJ.APPLY_DATE) INVOICE_DATE
, DECODE(GCC.ACCOUNT_TYPE
, 'A'
, NVL(AD.ACCTD_AMOUNT_DR
, 0) - NVL(AD.ACCTD_AMOUNT_CR
, 0)
, NVL(AD.ACCTD_AMOUNT_CR
, 0) - NVL(AD.ACCTD_AMOUNT_DR
, 0)) AMT_B
, ADJ.GL_DATE GL_DATE_FILTER
FROM EDW_LOCAL_INSTANCE LI
, AR_ADJUSTMENTS_ALL ADJ
, AR_DISTRIBUTIONS_ALL AD
, GL_CODE_COMBINATIONS GCC
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
WHERE NVL(ADJ.STATUS
, 'A') = 'A'
AND NVL(ADJ.POSTABLE
, 'Y') = 'Y'
AND AD.SOURCE_ID = ADJ.ADJUSTMENT_ID
AND AD.SOURCE_TABLE = 'ADJ'
AND CT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND NVL(CT.ORG_ID
, -999) = NVL(ADJ.ORG_ID
, -999)
AND CT.COMPLETE_FLAG = 'Y'
AND SOB.SET_OF_BOOKS_ID = ADJ.SET_OF_BOOKS_ID
AND AD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID UNION ALL SELECT /*+ USE_NL(CTL SL_CHILD CT SOB GCC) */ 'NON-GEMMS' VIEW_TYPE
, SOB.CURRENCY_CODE BASE_CURRENCY
, SOB.SET_OF_BOOKS_ID||'-'|| LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE(CTLGD.ORG_ID
, NULL
, 'NA_EDW'
, CTLGD.ORG_ID||'-'|| LI.INSTANCE_CODE) OU_ORG_FK
, DECODE(CT.BILL_TO_SITE_USE_ID
, NULL
, 'NA_EDW'
, CT.BILL_TO_SITE_USE_ID||'-'|| LI.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_CUSTOMER_FK
, DECODE(GCC.COMPANY_COST_CENTER_ORG_ID
, NULL
, 'NA_EDW'
, GCC.COMPANY_COST_CENTER_ORG_ID||'-'||LI.INSTANCE_CODE) COMPANY_CC_ORG_FK
, TRUNC(CTLGD.GL_DATE) GL_DATE
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, LI.INSTANCE_CODE INSTANCE_FK
, DECODE(SL_CHILD.INVENTORY_ITEM_ID
, NULL
, 'NA_EDW'
, DECODE(SL_CHILD.SHIP_FROM_ORG_ID
, NULL
, 'NA_EDW'
, SL_CHILD.INVENTORY_ITEM_ID||'-'||SL_CHILD.SHIP_FROM_ORG_ID||'-'|| LI.INSTANCE_CODE||'-IORG')) ITEM_FK
, TRUNC(CT.TRX_DATE) INVOICE_DATE
, CTLGD.ACCTD_AMOUNT AMT_B
, CTLGD.GL_DATE GL_DATE_FILTER
FROM EDW_LOCAL_INSTANCE LI
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
, GL_CODE_COMBINATIONS GCC
, OE_ORDER_LINES_ALL SL_CHILD
WHERE CTLGD.ACCOUNT_SET_FLAG = 'N'
AND NVL(CTLGD.AMOUNT
, 0) <> 0
AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CTL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND DECODE(CTL.INTERFACE_LINE_ATTRIBUTE6
, NULL
, NULL
, TRANSLATE(CTL.INTERFACE_LINE_ATTRIBUTE6
, 'Z0123456789'
, 'Z')) IS NULL
AND CT.COMPLETE_FLAG = 'Y'
AND CTLGD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND SL_CHILD.LINE_ID (+) = TO_NUMBER(CTL.INTERFACE_LINE_ATTRIBUTE6) UNION ALL SELECT /*+ USE_NL(CTL CT OOC OOD IIM MSI IWM SOB GCC) */ 'GEMMS' VIEW_TYPE
, SOB.CURRENCY_CODE BASE_CURRENCY
, SOB.SET_OF_BOOKS_ID||'-'|| LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE(CTLGD.ORG_ID
, NULL
, 'NA_EDW'
, CTLGD.ORG_ID||'-'|| LI.INSTANCE_CODE) OU_ORG_FK
, DECODE(CT.BILL_TO_SITE_USE_ID
, NULL
, 'NA_EDW'
, CT.BILL_TO_SITE_USE_ID||'-'|| LI.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_CUSTOMER_FK
, DECODE(GCC.COMPANY_COST_CENTER_ORG_ID
, NULL
, 'NA_EDW'
, GCC.COMPANY_COST_CENTER_ORG_ID||'-'||LI.INSTANCE_CODE) COMPANY_CC_ORG_FK
, TRUNC(CTLGD.GL_DATE) GL_DATE
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, LI.INSTANCE_CODE INSTANCE_FK
, DECODE(MSI.INVENTORY_ITEM_ID
, NULL
, 'NA_EDW'
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||LI.INSTANCE_CODE||'-IORG') ITEM_FK
, TRUNC(CT.TRX_DATE) INVOICE_DATE
, OPI_OPM_COMMON_PKG.OPI_OPM_GET_CHARGE(OOD.ORDER_ID
, CTLGD.ACCTD_AMOUNT
, OOD.EXTENDED_PRICE
, OOD.BILLING_CURRENCY
, OOD.BASE_CURRENCY
, OOD.EXCHANGE_RATE
, OOD.MUL_DIV_SIGN) AMT_B
, CTLGD.GL_DATE GL_DATE_FILTER
FROM EDW_LOCAL_INSTANCE LI
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, GL_CODE_COMBINATIONS GCC
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
, OP_ORDR_DTL OOD
, OP_ORDR_CHG OOC
, IC_WHSE_MST IWM
, IC_ITEM_MST IIM
, MTL_SYSTEM_ITEMS MSI
WHERE CTLGD.ACCOUNT_SET_FLAG = 'N'
AND NVL(CTLGD.AMOUNT
, 0) <> 0
AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND NVL(CTL.INTERFACE_LINE_CONTEXT
, 'XXX') IN ('GEMMS OP')
AND CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CTLGD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND OOD.FROM_WHSE = IWM.WHSE_CODE
AND CTL.INTERFACE_LINE_ATTRIBUTE1 = '0'
AND CTL.INTERFACE_LINE_ATTRIBUTE7 = OOC.CHARGE_LINE_ID
AND OOD.ORDER_ID = OOC.ORDER_ID
AND OOD.ITEM_ID = IIM.ITEM_ID
AND IIM.ITEM_NO = MSI.SEGMENT1
AND IWM.MTL_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND OOD.LINE_STATUS >= 20 UNION ALL SELECT /*+ USE_NL(CTL CT OOC OOD IIM MSI IWM SOB GCC) */ 'GEMMS' VIEW_TYPE
, SOB.CURRENCY_CODE BASE_CURRENCY
, SOB.SET_OF_BOOKS_ID||'-'|| LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE(CTLGD.ORG_ID
, NULL
, 'NA_EDW'
, CTLGD.ORG_ID||'-'|| LI.INSTANCE_CODE) OU_ORG_FK
, DECODE(CT.BILL_TO_SITE_USE_ID
, NULL
, 'NA_EDW'
, CT.BILL_TO_SITE_USE_ID||'-'|| LI.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_CUSTOMER_FK
, DECODE(GCC.COMPANY_COST_CENTER_ORG_ID
, NULL
, 'NA_EDW'
, GCC.COMPANY_COST_CENTER_ORG_ID||'-'||LI.INSTANCE_CODE) COMPANY_CC_ORG_FK
, TRUNC(CTLGD.GL_DATE) GL_DATE
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, LI.INSTANCE_CODE INSTANCE_FK
, DECODE(MSI.INVENTORY_ITEM_ID
, NULL
, 'NA_EDW'
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||LI.INSTANCE_CODE||'-IORG') ITEM_FK
, TRUNC(CT.TRX_DATE) INVOICE_DATE
, OPI_OPM_COMMON_PKG.OPI_OPM_GET_CHARGE(OOD.ORDER_ID
, CTLGD.ACCTD_AMOUNT
, OOD.EXTENDED_PRICE
, OOD.BILLING_CURRENCY
, OOD.BASE_CURRENCY
, OOD.EXCHANGE_RATE
, OOD.MUL_DIV_SIGN) AMT_B
, CTLGD.GL_DATE GL_DATE_FILTER
FROM EDW_LOCAL_INSTANCE LI
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, GL_CODE_COMBINATIONS GCC
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
, OP_ORDR_DTL OOD
, IC_WHSE_MST IWM
, IC_ITEM_MST IIM
, MTL_SYSTEM_ITEMS MSI
WHERE CTLGD.ACCOUNT_SET_FLAG = 'N'
AND NVL(CTLGD.AMOUNT
, 0) <> 0
AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND NVL(CTL.INTERFACE_LINE_CONTEXT
, 'XXX') IN ('GEMMS OP')
AND CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CTLGD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND CTL.INTERFACE_LINE_ATTRIBUTE1 <> '0'
AND OOD.LINE_ID = CTL.INTERFACE_LINE_ATTRIBUTE1
AND OOD.ITEM_ID = IIM.ITEM_ID
AND OOD.FROM_WHSE = IWM.WHSE_CODE
AND IIM.ITEM_NO = MSI.SEGMENT1
AND IWM.MTL_ORGANIZATION_ID = MSI.ORGANIZATION_ID

Columns

Name
VIEW_TYPE
BASE_CURRENCY
SET_OF_BOOKS_FK
OU_ORG_FK
CUSTOMER_FK
ORG_FK
GL_DATE
SET_OF_BOOKS_ID
INSTANCE
PRODUCT_FK
INVOICE_DATE
AMOUNT_B
GL_DATE_FILTER