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.

VIEW: APPS.FIIBV_AP_INV_LINES_FCV

Source

View Text - Preformatted


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

View Text - HTML Formatted

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