View: FIIBV_AP_INV_LINES_FCV

Product:	FII - Financial Intelligence
Description:	FIIBV_AP_INV_LINES_FCV is the base view for FII_AP_INV_LINES_F fact
Implementation/DBA Data:	APPS.FIIBV_AP_INV_LINES_FCV

View Text

SELECT /*+ ORDERED USE_NL(AID AI GCC GSOB PV PD PL PLL PH MTL FU FSP LI LS) INDEX(AID AP_INVOICE_DISTRIBUTIONS_U1) INDEX(AI AP_INVOICES_U1) INDEX(GCC GL_CODE_COMBINATIONS_U1) INDEX(GSOB GL_SETS_OF_BOOKS_U2) INDEX(PV PO_VENDORS_U1) INDEX(PD PO_DISTRIBUTIONS_U1) INDEX(PLL PO_LINE_LOCATIONS_U1) INDEX(PH PO_HEADERS_U1) INDEX(MTL MTL_UNITS_OF_MEASURE_TL_U1) INDEX(FU FND_USER_U1) */ AI.APPROVAL_STATUS
, FTP.SEQ_ID
, TO_CHAR(AID.INVOICE_ID)||'-'||TO_CHAR(AID.DISTRIBUTION_LINE_NUMBER)|| '-'|| TO_CHAR(AID.INVOICE_LINE_NUMBER)|| '-'||TO_CHAR(AID.ORG_ID)||'-'|| LI.INSTANCE_CODE INV_LINE_PK
, EDW_UTIL.GET_EDW_BASE_UOM(MTL.UOM_CODE
, PL.ITEM_ID) UOM_G_FK
, EDW_UTIL.GET_EDW_UOM(MTL.UOM_CODE
, PL.ITEM_ID) UOM_T_FK
, DECODE(AID.ACCOUNTING_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(AID.ACCOUNTING_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') ACCOUNTING_DATE_FK
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FU.EMPLOYEE_ID)||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS') EMPLOYEE_FK
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AID.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') GEOGRAPHY_FK
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') SUPPLIER_FK
, DECODE(AI.INVOICE_ID
, NULL
, 'NA_EDW'
, (TO_CHAR(AI.INVOICE_ID) || '-' || TO_CHAR(AI.ORG_ID) || '-' || LI.INSTANCE_CODE)) INV_FK
, NVL(AI.INVOICE_CURRENCY_CODE
, 'NA_EDW') INV_CURRENCY_FK
, LI.INSTANCE_CODE INSTANCE_FK
, NVL(AI.SOURCE
, 'NA_EDW') INV_SOURCE_FK
, DECODE(AID.ORG_ID
, NULL
, 'NA_EDW'
, AID.ORG_ID||'-'||LI.INSTANCE_CODE) ORG_FK
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, (TO_CHAR(AI.TERMS_ID)||'-AP-'||LI.INSTANCE_CODE)) PAYMENT_TERM_FK
, DECODE(AI.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.SET_OF_BOOKS_ID)||'-'|| LI.INSTANCE_CODE) SOB_FK
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW') SIC_CODE_FK
, 'NA_EDW' DUNS_FK
, 'NA_EDW' UNSPSC_FK
, NVL(DECODE(NVL(FSP.INVENTORY_ORGANIZATION_ID
, PL.ORG_ID)
, NULL
, 'NA_EDW'
, DECODE(PL.ITEM_ID
, NULL
, PL.ITEM_DESCRIPTION||'-'|| TO_CHAR(PL.CATEGORY_ID)||'-'|| TO_CHAR(PL.ORG_ID)||'-'|| LI.INSTANCE_CODE||'-ONETIME-IORG'
, TO_CHAR(PL.ITEM_ID)||'-'|| TO_CHAR(FSP.INVENTORY_ORGANIZATION_ID)||'-'|| LI.INSTANCE_CODE||'-IORG'))
, 'NA_EDW') ITEM_FK
, NVL(AID.LINE_TYPE_LOOKUP_CODE
, 'NA_EDW') INV_LINE_TYPE_FK
, SUBSTRB(LI.INSTANCE_CODE
, 1
, 40) INSTANCE
, 1 MATCH_LINE_COUNT
, 0 UNMATCH_LINE_COUNT
, 1 INV_LINE_COUNT
, AID.AMOUNT MATCH_LINE_AMT_T
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT) MATCH_LINE_AMT_B
, TO_NUMBER(NULL) MATCH_LINE_AMT_G
, 0 UNMATCH_LINE_AMT_T
, 0 UNMATCH_LINE_AMT_B
, 0 UNMATCH_LINE_AMT_G
, AID.AMOUNT INV_LINE_AMT_T
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT) INV_LINE_AMT_B
, TO_NUMBER(NULL) INV_LINE_AMT_G
, DECODE(AID.UNIT_PRICE
, NULL
, TO_NUMBER(NULL)
, AID.UNIT_PRICE) INV_UNIT_PRICE_T
, DECODE(AID.UNIT_PRICE
, NULL
, TO_NUMBER(NULL)
, AID.UNIT_PRICE * AI.EXCHANGE_RATE) INV_UNIT_PRICE_B
, TO_NUMBER(NULL) INV_UNIT_PRICE_G
, DECODE(PLL.PRICE_OVERRIDE
, NULL
, TO_NUMBER(NULL)
, PLL.PRICE_OVERRIDE * NVL(PH.RATE
, 1)) PO_UNIT_PRICE_B
, TO_NUMBER(NULL) PO_UNIT_PRICE_G
, NVL(PLL.PRICE_OVERRIDE
, TO_NUMBER(NULL)) PO_UNIT_PRICE_T
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, NVL(AID.AMOUNT
, 0)
, NVL(AID.QUANTITY_VARIANCE
, 0)) TOTAL_VAR_AMT_T
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, NVL(AID.BASE_AMOUNT
, NVL(AID.AMOUNT
, 0))
, 'ERV'
, NVL(AID.AMOUNT
, 0)
, NVL(AID.BASE_QUANTITY_VARIANCE
, NVL(AID.QUANTITY_VARIANCE
, 0))) TOTAL_VAR_AMT_B
, TO_NUMBER(NULL) TOTAL_VAR_AMT_G
, TRUNC(AID.ACCOUNTING_DATE) ACCOUNTING_DATE
, AI.VENDOR_SITE_ID SUPPLIER_SITE_ID
, SUBSTRB(AI.INVOICE_NUM
, 1
, 50) INV_NUM
, SUBSTRB(AI.INVOICE_TYPE_LOOKUP_CODE
, 1
, 25) INV_TYPE
, GSOB.CURRENCY_CODE BASE_CURRENCY_CODE
, PL.ITEM_ID ITEM_ID
, PL.CATEGORY_ID CATEGORY_ID
, SUBSTRB(PL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, AID.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, SUBSTRB(AID.ACCRUAL_POSTED_FLAG
, 1
, 1) ACCRUAL_POSTED_FLAG
, SUBSTRB(AID.AMOUNT_INCLUDES_TAX_FLAG
, 1
, 1) AMT_INCLUDES_TAX_FLAG
, SUBSTRB(AID.ASSETS_TRACKING_FLAG
, 1
, 1) ASSETS_TRACKING_FLAG
, SUBSTRB(AID.CASH_POSTED_FLAG
, 1
, 1) CASH_POSTED_FLAG
, SUBSTRB(AID.POSTED_FLAG
, 1
, 1) POSTED_FLAG
, SUBSTRB(AID.MATCH_STATUS_FLAG
, 1
, 1) MATCH_STATUS_FLAG
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, NVL(AID.AMOUNT
, 0)
, 0) INV_PRICE_VAR_AMT_T
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, NVL(AID.BASE_AMOUNT
, NVL(AID.AMOUNT
, 0))
, 0) INV_PRICE_VAR_AMT_B
, TO_NUMBER(NULL) INV_PRICE_VAR_AMT_G
, AID.QUANTITY_VARIANCE QTY_VAR_AMT_T
, NVL(AID.BASE_QUANTITY_VARIANCE
, AID.QUANTITY_VARIANCE) QTY_VAR_AMT_B
, TO_NUMBER(NULL) QTY_VAR_AMT_G
, AID.QUANTITY_INVOICED QUANTITY_INVOICED_T
, AID.QUANTITY_INVOICED * EDW_UTIL.GET_UOM_CONV_RATE (MTL.UOM_CODE
, PL.ITEM_ID) QUANTITY_INVOICED_G
, AID.BATCH_ID BATCH_ID
, SUBSTRB(AID.DESCRIPTION
, 1
, 240) INV_LINE_DESCRIPTION
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'ERV'
, NVL(AID.AMOUNT
, 0)
, 0) EXCHANGE_RATE_VAR
, SUBSTRB(AID.INCOME_TAX_REGION
, 1
, 10) INCOME_TAX_REGION
, TRUNC(AI.INVOICE_DATE) INV_DATE
, SUBSTRB(AID.TYPE_1099
, 1
, 10) TYPE_1099
, SUBSTRB(AID.VAT_CODE
, 1
, 15) VAT_CODE
, SUBSTRB(AID.ENCUMBERED_FLAG
, 1
, 1) ENCUMBERED_FLAG
, AID.PROJECT_ID PROJECT_ID
, AID.TASK_ID TASK_ID
, AI.EXCHANGE_RATE EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, TRUNC(AI.EXCHANGE_DATE) EXCHANGE_DATE
, AID.CASH_JE_BATCH_ID CASH_JE_BATCH_ID
, SUBSTRB(AID.AWT_FLAG
, 1
, 1) AWT_FLAG
, AID.AWT_GROUP_ID AWT_GROUP_ID
, AID.POSTED_AMOUNT POSTED_AMT_T
, NVL(AID.POSTED_BASE_AMOUNT
, AID.POSTED_AMOUNT) POSTED_AMT_B
, TO_NUMBER(NULL) POSTED_AMT_G
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, 0
, NVL(AID.AMOUNT
, 0) - NVL(AID.QUANTITY_VARIANCE
, 0)) PO_AMT_T
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'IPV'
, 0
, NVL(AID.BASE_AMOUNT
, NVL(AID.AMOUNT
, 0)) - NVL(AID.BASE_QUANTITY_VARIANCE
, NVL(AID.QUANTITY_VARIANCE
, 0))) PO_AMT_B
, TO_NUMBER(NULL) PO_AMT_G
, AID.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE1
, PH.LAST_UPDATE_DATE LAST_UPDATE_DATE2
, PL.LAST_UPDATE_DATE LAST_UPDATE_DATE3
, PLL.LAST_UPDATE_DATE LAST_UPDATE_DATE4
, AID.CREATION_DATE CREATION_DATE
, AID.DIST_CODE_COMBINATION_ID CCID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, NVL( EDW_CURRENCY.GET_RATE ( GSOB.CURRENCY_CODE
, DECODE(FTP.PRIMARY_KEY5
, 1
, AID.ACCOUNTING_DATE
, AI.INVOICE_DATE)
, AI.EXCHANGE_RATE_TYPE)
, -1) GLOBAL_CURRENCY_RATE
, SUBSTRB(PH.SEGMENT1
, 1
, 20) PO_NUMBER
, 'NA_EDW' GL_ACCT1_FK
, 'NA_EDW' GL_ACCT2_FK
, 'NA_EDW' GL_ACCT3_FK
, 'NA_EDW' GL_ACCT4_FK
, 'NA_EDW' GL_ACCT5_FK
, 'NA_EDW' GL_ACCT6_FK
, 'NA_EDW' GL_ACCT7_FK
, 'NA_EDW' GL_ACCT8_FK
, 'NA_EDW' GL_ACCT9_FK
, 'NA_EDW' GL_ACCT10_FK
, '_KF:SQLGL:GL#:GCC'
, '_DF:SQLAP:AP_INVOICE_DISTRIBUTIONS:AID'
, '_DF:JG:JG_AP_INVOICE_DISTRIBUTIONS:AID'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, DECODE(AID.PROJECT_ID
, NULL
, 'NA_EDW'
, AID.PROJECT_ID || '-' || LI.INSTANCE_CODE || '-PJ-PRJ')
, AID.EXPENDITURE_TYPE
, AI.VOUCHER_NUM
, AI.DOC_SEQUENCE_VALUE
, AID.RCV_TRANSACTION_ID RCV_TRANSACTION_ID
, AID.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, AID.OLD_DIST_LINE_NUMBER OLD_DIST_LINE_NUMBER
FROM FII_AP_TMP_LINE_PK FTP
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AI
, GL_SETS_OF_BOOKS GSOB
, PO_DISTRIBUTIONS_ALL PD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, PO_HEADERS_ALL PH
, PO_VENDORS PV
, FND_USER FU
, GL_CODE_COMBINATIONS GCC
, MTL_UNITS_OF_MEASURE MTL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, EDW_LOCAL_INSTANCE LI
, EDW_LOCAL_SYSTEM_PARAMETERS LS
WHERE FTP.PRIMARY_KEY1 = AID.INVOICE_ID
AND FTP.PRIMARY_KEY2 = AID.DISTRIBUTION_LINE_NUMBER
AND FTP.PRIMARY_KEY4 = AID.INVOICE_LINE_NUMBER
AND AID.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AID.INVOICE_ID = AI.INVOICE_ID
AND AID.ORG_ID = AI.ORG_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.CREATED_BY = FU.USER_ID (+)
AND AID.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID
AND AID.ORG_ID = PD.ORG_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.ORG_ID = PL.ORG_ID
AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.SHIPMENT_TYPE <> 'PREPAYMENT'
AND PD.ORG_ID = PLL.ORG_ID
AND PL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PL.ORG_ID = PH.ORG_ID
AND PL.UNIT_MEAS_LOOKUP_CODE = MTL.UNIT_OF_MEASURE
AND GCC.CODE_COMBINATION_ID = AID.DIST_CODE_COMBINATION_ID
AND PL.ORG_ID = FSP.ORG_ID (+) UNION ALL SELECT /*+ ORDERED USE_NL(AID AI GCC GSOB PV FU LI LS) INDEX(AID AP_INVOICE_DISTRIBUTIONS_U1) INDEX(AI AP_INVOICES_U1) INDEX(GCC GL_CODE_COMBINATIONS_U1) INDEX(GSOB GL_SETS_OF_BOOKS_U2) INDEX(PV PO_VENDORS_U1) INDEX(FU FND_USER_U1) */ AI.APPROVAL_STATUS
, FTP.SEQ_ID
, TO_CHAR(AID.INVOICE_ID)||'-'||TO_CHAR(AID.DISTRIBUTION_LINE_NUMBER)|| '-'|| TO_CHAR(AID.INVOICE_LINE_NUMBER)|| '-'||TO_CHAR(AID.ORG_ID)||'-'|| LI.INSTANCE_CODE INV_LINE_PK
, 'NA_EDW' UOM_G_FK
, 'NA_EDW' UOM_T_FK
, DECODE(AID.ACCOUNTING_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(AID.ACCOUNTING_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') ACCOUNTING_DATE_FK
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, FU.EMPLOYEE_ID||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS') EMPLOYEE_FK
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AID.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') GEOGRAPHY_FK
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') SUPPLIER_FK
, DECODE(AI.INVOICE_ID
, NULL
, 'NA_EDW'
, (TO_CHAR(AI.INVOICE_ID) || '-' || TO_CHAR(AI.ORG_ID) || '-' || LI.INSTANCE_CODE)) INV_FK
, NVL(AI.INVOICE_CURRENCY_CODE
, 'NA_EDW') CURRENCY_FK
, LI.INSTANCE_CODE INSTANCE_FK
, NVL(AI.SOURCE
, 'NA_EDW') INV_SOURCE_FK
, DECODE(AID.ORG_ID
, NULL
, 'NA_EDW'
, AID.ORG_ID||'-'||LI.INSTANCE_CODE) ORG_FK
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, (TO_CHAR(AI.TERMS_ID)||'-AP-'||LI.INSTANCE_CODE)) PAYMENT_TERM_FK
, DECODE(AI.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.SET_OF_BOOKS_ID)||'-'|| LI.INSTANCE_CODE) SOB_FK
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW') SIC_CODE_FK
, 'NA_EDW' DUNS_FK
, 'NA_EDW' UNSPSC_FK
, 'NA_EDW' ITEM_FK
, NVL(AID.LINE_TYPE_LOOKUP_CODE
, 'NA_EDW') INV_LINE_TYPE_FK
, SUBSTRB(LI.INSTANCE_CODE
, 1
, 40) INSTANCE
, 0 MATCH_LINE_COUNT
, 1 UNMATCH_LINE_COUNT
, 1 INV_LINE_COUNT
, 0 MATCH_LINE_AMT_T
, 0 MATCH_LINE_AMT_B
, 0 MATCH_LINE_AMT_G
, AID.AMOUNT UNMATCH_LINE_AMT_T
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT) UNMATCH_LINE_AMT_B
, TO_NUMBER(NULL) UNMATCH_LINE_AMT_G
, AID.AMOUNT INV_LINE_AMT_T
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT) INV_LINE_AMT_B
, TO_NUMBER(NULL) INV_LINE_AMT_G
, TO_NUMBER(NULL) INV_UNIT_PRICE_T
, TO_NUMBER(NULL) INV_UNIT_PRICE_B
, TO_NUMBER(NULL) INV_UNIT_PRICE_G
, TO_NUMBER(NULL) PO_UNIT_PRICE_B
, TO_NUMBER(NULL) PO_UNIT_PRICE_G
, TO_NUMBER(NULL) PO_UNIT_PRICE_T
, TO_NUMBER(NULL) TOTAL_VAR_AMT_T
, TO_NUMBER(NULL) TOTAL_VAR_AMT_B
, TO_NUMBER(NULL) TOTAL_VAR_AMT_G
, TRUNC(AID.ACCOUNTING_DATE) ACCOUNTING_DATE
, AI.VENDOR_SITE_ID SUPPLIER_SITE_ID
, SUBSTRB(AI.INVOICE_NUM
, 1
, 50) INV_NUM
, SUBSTRB(AI.INVOICE_TYPE_LOOKUP_CODE
, 1
, 25) INV_TYPE
, GSOB.CURRENCY_CODE BASE_CURRENCY_CODE
, TO_NUMBER(NULL) ITEM_ID
, TO_NUMBER(NULL) CATEGORY_ID
, TO_CHAR(NULL) ITEM_DESCRIPTION
, TO_NUMBER(NULL) PO_DISTRIBUTION_ID
, SUBSTRB(AID.ACCRUAL_POSTED_FLAG
, 1
, 1) ACCRUAL_POSTED_FLAG
, SUBSTRB(AID.AMOUNT_INCLUDES_TAX_FLAG
, 1
, 1) AMT_INCLUDES_TAX_FLAG
, SUBSTRB(AID.ASSETS_TRACKING_FLAG
, 1
, 1) ASSETS_TRACKING_FLAG
, SUBSTRB(AID.CASH_POSTED_FLAG
, 1
, 1) CASH_POSTED_FLAG
, SUBSTRB(AID.POSTED_FLAG
, 1
, 1) POSTED_FLAG
, SUBSTRB(AID.MATCH_STATUS_FLAG
, 1
, 1) MATCH_STATUS_FLAG
, TO_NUMBER(NULL) INV_PRICE_VAR_AMT_T
, TO_NUMBER(NULL) INV_PRICE_VAR_AMT_B
, TO_NUMBER(NULL) INV_PRICE_VAR_AMT_G
, TO_NUMBER(NULL) QTY_VAR_AMT_T
, TO_NUMBER(NULL) QTY_VAR_AMT_B
, TO_NUMBER(NULL) QTY_VAR_AMT_G
, TO_NUMBER(NULL) QUANTITY_INVOICED_T
, TO_NUMBER(NULL) QUANTITY_INVOICED_G
, AID.BATCH_ID BATCH_ID
, SUBSTRB(AID.DESCRIPTION
, 1
, 240) INV_LINE_DESCRIPTION
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'ERV'
, NVL(AID.AMOUNT
, 0)
, 0) EXCHANGE_RATE_VAR
, SUBSTRB(AID.INCOME_TAX_REGION
, 1
, 10) INCOME_TAX_REGION
, TRUNC(AI.INVOICE_DATE) INV_DATE
, SUBSTRB(AID.TYPE_1099
, 1
, 10) TYPE_1099
, SUBSTRB(AID.VAT_CODE
, 1
, 15) VAT_CODE
, SUBSTRB(AID.ENCUMBERED_FLAG
, 1
, 1) ENCUMBERED_FLAG
, AID.PROJECT_ID PROJECT_ID
, AID.TASK_ID TASK_ID
, AI.EXCHANGE_RATE EXCHANGE_RATE
, SUBSTRB(AI.EXCHANGE_RATE_TYPE
, 1
, 30) EXCHANGE_RATE_TYPE
, TRUNC(AI.EXCHANGE_DATE) EXCHANGE_DATE
, AID.CASH_JE_BATCH_ID CASH_JE_BATCH_ID
, SUBSTRB(AID.AWT_FLAG
, 1
, 1) AWT_FLAG
, AID.AWT_GROUP_ID AWT_GROUP_ID
, AID.POSTED_AMOUNT POSTED_AMT_T
, NVL(AID.POSTED_BASE_AMOUNT
, AID.POSTED_AMOUNT) POSTED_AMT_B
, TO_NUMBER(NULL) POSTED_AMT_G
, TO_NUMBER(NULL) PO_AMT_T
, TO_NUMBER(NULL) PO_AMT_B
, TO_NUMBER(NULL) PO_AMT_G
, AID.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE1
, TO_DATE(NULL) LAST_UPDATE_DATE2
, TO_DATE(NULL) LAST_UPDATE_DATE3
, TO_DATE(NULL) LAST_UPDATE_DATE4
, AID.CREATION_DATE CREATION_DATE
, AID.DIST_CODE_COMBINATION_ID CCID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, NVL( EDW_CURRENCY.GET_RATE ( GSOB.CURRENCY_CODE
, DECODE(FTP.PRIMARY_KEY5
, 1
, AID.ACCOUNTING_DATE
, AI.INVOICE_DATE)
, AI.EXCHANGE_RATE_TYPE)
, -1) GLOBAL_CURRENCY_RATE
, TO_CHAR(NULL) PO_NUMBER
, 'NA_EDW' GL_ACCT1_FK
, 'NA_EDW' GL_ACCT2_FK
, 'NA_EDW' GL_ACCT3_FK
, 'NA_EDW' GL_ACCT4_FK
, 'NA_EDW' GL_ACCT5_FK
, 'NA_EDW' GL_ACCT6_FK
, 'NA_EDW' GL_ACCT7_FK
, 'NA_EDW' GL_ACCT8_FK
, 'NA_EDW' GL_ACCT9_FK
, 'NA_EDW' GL_ACCT10_FK
, '_KF:SQLGL:GL#:GCC'
, '_DF:SQLAP:AP_INVOICE_DISTRIBUTIONS:AID'
, '_DF:JG:JG_AP_INVOICE_DISTRIBUTIONS:AID'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, DECODE(AID.PROJECT_ID
, NULL
, 'NA_EDW'
, AID.PROJECT_ID || '-' || LI.INSTANCE_CODE || '-PJ-PRJ')
, AID.EXPENDITURE_TYPE
, AI.VOUCHER_NUM
, AI.DOC_SEQUENCE_VALUE
, AID.RCV_TRANSACTION_ID RCV_TRANSACTION_ID
, AID.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, AID.OLD_DIST_LINE_NUMBER OLD_DIST_LINE_NUMBER
FROM FII_AP_TMP_LINE_PK FTP
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AI
, GL_SETS_OF_BOOKS GSOB
, PO_VENDORS PV
, FND_USER FU
, GL_CODE_COMBINATIONS GCC
, EDW_LOCAL_INSTANCE LI
, EDW_LOCAL_SYSTEM_PARAMETERS LS
WHERE FTP.PRIMARY_KEY1 = AID.INVOICE_ID
AND FTP.PRIMARY_KEY2 = AID.DISTRIBUTION_LINE_NUMBER
AND FTP.PRIMARY_KEY4 = AID.INVOICE_LINE_NUMBER
AND AID.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AID.INVOICE_ID = AI.INVOICE_ID
AND AID.ORG_ID = AI.ORG_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND GCC.CODE_COMBINATION_ID = AID.DIST_CODE_COMBINATION_ID
AND AI.CREATED_BY = FU.USER_ID (+)
AND AID.PO_DISTRIBUTION_ID IS NULL

Columns