VIEW: APPS.AP_EXPENSE_FEED_DISTS_OPEN_V

Source

View Text - Preformatted

SELECT EFD.FEED_LINE_ID , EFD.FEED_DISTRIBUTION_ID , EFD.INVOICE_ID , EFD.INVOICE_LINE_ID , EFD.AMOUNT , EFD.DIST_CODE_COMBINATION_ID , EFD.STATUS_CHANGE_DATE , EFD.STATUS_LOOKUP_CODE , EFD.INVOICED_FLAG , EFD.AMOUNT_INCLUDES_TAX_FLAG , EFD.TAX_CODE , EFD.MANAGER_APPROVAL_ID , EFD.EMPLOYEE_VERIFICATION_ID , EFD.ACCOUNT_SEGMENT_VALUE , EFD.COST_CENTER , EFD.ORG_ID , EFD.LAST_UPDATE_DATE , EFD.LAST_UPDATED_BY , EFD.LAST_UPDATE_LOGIN , EFD.CREATION_DATE , EFD.CREATED_BY , EFD.ATTRIBUTE_CATEGORY , EFD.ATTRIBUTE1 , EFD.ATTRIBUTE2 , EFD.ATTRIBUTE3 , EFD.ATTRIBUTE4 , EFD.ATTRIBUTE5 , EFD.ATTRIBUTE6 , EFD.ATTRIBUTE7 , EFD.ATTRIBUTE8 , EFD.ATTRIBUTE9 , EFD.ATTRIBUTE10 , EFD.ATTRIBUTE11 , EFD.ATTRIBUTE12 , EFD.ATTRIBUTE13 , EFD.ATTRIBUTE14 , EFD.ATTRIBUTE15 , EFL.EMPLOYEE_ID , EFL.CARD_ID , EFL.CARD_PROGRAM_ID , IBY.CARD_NUMBER , EFL.REFERENCE_NUMBER , EFL.ALT_REFERENCE_NUMBER , EFL.CUSTOMER_CODE , EFL.AMOUNT LINE_AMOUNT , EFL.ORIGINAL_CURRENCY_AMOUNT , EFL.ORIGINAL_CURRENCY_CODE , EFL.POSTED_CURRENCY_CODE , EFL.CURRENCY_CONVERSION_RATE , EFL.TRANSACTION_DATE , EFL.POSTED_DATE , EFL.RECORD_TYPE , EFL.CREATE_DISTRIBUTION_FLAG , EFL.MERCHANT_NAME , EFL.MERCHANT_NUMBER , EFL.CARD_CODE_VALUE , EFL.CODE_ID , EFL.INDUSTRY_CODE1 , EFL.INDUSTRY_CODE2 , EFL.ALT_MERCHANT_NUMBER , EFL.MERCHANT_ADDRESS1 , EFL.MERCHANT_ADDRESS2 , EFL.MERCHANT_ADDRESS3 , EFL.MERCHANT_ADDRESS4 , EFL.MERCHANT_CITY , EFL.MERCHANT_PROVINCE_STATE , EFL.MERCHANT_POSTAL_CODE , EFL.MERCHANT_COUNTRY , EFL.MERCHANT_PHONE , EFL.SHIP_FROM_POSTAL_CODE , EFL.SHIP_FROM_COUNTRY , EFL.SHIP_TO_POSTAL_CODE , EFL.SHIP_TO_COUNTRY , EFL.MERCHANT_TAX_ID , EFL.TAX_PAID_FLAG , EFL.TAX_AMOUNT , EFL.TAX_RATE , EFL.TAX_TYPE , EFL.ALT_MERCHANT_TAX_ID , EFL.ALT_TAX_PAID_FLAG , EFL.ALT_TAX_AMOUNT , EFL.ALT_TAX_RATE , EFL.ALT_TAX_TYPE , EFL.FREIGHT_AMOUNT , EFL.DUTY_AMOUNT , EFL.MERCHANT_BUSINESS_TYPE , EFL.MERCHANT_OWNER_TYPE , EFL.PRODUCT_CODE , EFL.ITEM_DESCRIPTION , EFL.ITEM_QUANTITY , EFL.ITEM_UOM , EFL.EXTENDED_ITEM_AMOUNT , EFL.DISCOUNT_INDICATOR , EFL.DISCOUNT_AMOUNT , EFL.EMPLOYEE_VERIFICATION_ID LINE_EMP_VERIFICATION_ID , EFL.DESCRIPTION LINE_DESCRIPTION , EFL.REJECT_CODE , EFL.ATTRIBUTE_CATEGORY LINE_ATTRIBUTE_CATEGORY , EFL.ATTRIBUTE1 LINE_ATTRIBUTE1 , EFL.ATTRIBUTE2 LINE_ATTRIBUTE2 , EFL.ATTRIBUTE3 LINE_ATTRIBUTE3 , EFL.ATTRIBUTE4 LINE_ATTRIBUTE4 , EFL.ATTRIBUTE5 LINE_ATTRIBUTE5 , EFL.ATTRIBUTE6 LINE_ATTRIBUTE6 , EFL.ATTRIBUTE7 LINE_ATTRIBUTE7 , EFL.ATTRIBUTE8 LINE_ATTRIBUTE8 , EFL.ATTRIBUTE9 LINE_ATTRIBUTE9 , EFL.ATTRIBUTE10 LINE_ATTRIBUTE10 , EFL.ATTRIBUTE11 LINE_ATTRIBUTE11 , EFL.ATTRIBUTE12 LINE_ATTRIBUTE12 , EFL.ATTRIBUTE13 LINE_ATTRIBUTE13 , EFL.ATTRIBUTE14 LINE_ATTRIBUTE14 , EFL.ATTRIBUTE15 LINE_ATTRIBUTE15 , EFL.ATTRIBUTE16 LINE_ATTRIBUTE16 , EFL.ATTRIBUTE17 LINE_ATTRIBUTE17 , EFL.ATTRIBUTE18 LINE_ATTRIBUTE18 , EFL.ATTRIBUTE19 LINE_ATTRIBUTE19 , EFL.ATTRIBUTE20 LINE_ATTRIBUTE20 , EFL.ATTRIBUTE21 LINE_ATTRIBUTE21 , EFL.ATTRIBUTE22 LINE_ATTRIBUTE22 , EFL.ATTRIBUTE23 LINE_ATTRIBUTE23 , EFL.ATTRIBUTE24 LINE_ATTRIBUTE24 , EFL.ATTRIBUTE25 LINE_ATTRIBUTE25 , EFL.ATTRIBUTE26 LINE_ATTRIBUTE26 , EFL.ATTRIBUTE27 LINE_ATTRIBUTE27 , EFL.ATTRIBUTE28 LINE_ATTRIBUTE28 , EFL.ATTRIBUTE29 LINE_ATTRIBUTE29 , EFL.ATTRIBUTE30 LINE_ATTRIBUTE30 , IBY.CARD_NUMBER SET_UP_CARD_NUMBER , C.EMPLOYEE_ID CARD_EMPLOYEE_ID , C.DEPARTMENT_NAME , CP.CARD_PROGRAM_NAME , CP.CARD_TYPE_LOOKUP_CODE , CPR.PROFILE_NAME , APL.DISPLAYED_FIELD DIST_STATUS , HREMP.FULL_NAME FULL_NAME , EFD.DESCRIPTION , '[PLSQL_AGENT]AP_CARD_VERIFY_PKG.VERIFY?P_FEED_DIST_ID='|| to_char(EFD.FEED_DISTRIBUTION_ID)|| '&'||'P_TICKET='|| FND_HTTP_TICKET.CREATE_TICKET('VERIFY PCARD',to_char(EFD.FEED_DISTRIBUTION_ID),86400) VERIFY_URL , HREMP.SUPERVISOR_ID , CP.ADMIN_EMPLOYEE_ID PROGRAM_ADMIN_EMPLOYEE_ID , CPR.ADMIN_EMPLOYEE_ID PROFILE_ADMIN_EMPLOYEE_ID , TO_CHAR(EFD.AMOUNT,FND_CURRENCY.GET_FORMAT_MASK(EFL.POSTED_CURRENCY_CODE,30)) DISPLAYED_AMOUNT , IBY.card_number masked_cc_number FROM AP_EXPENSE_FEED_DISTS EFD, AP_EXPENSE_FEED_LINES EFL, AP_CARDS C, AP_CARD_PROGRAMS CP, AP_CARD_PROFILES CPR, AP_LOOKUP_CODES APL, HR_EMPLOYEES_CURRENT_V HREMP, IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY WHERE EFL.FEED_LINE_ID = EFD.FEED_LINE_ID AND EFL.EMPLOYEE_ID = HREMP.EMPLOYEE_ID AND C.CARD_REFERENCE_ID = IBY.INSTRUMENT_ID AND EFL.CARD_PROGRAM_ID = CP.CARD_PROGRAM_ID AND C.PROFILE_ID = CPR.PROFILE_ID AND EFD.STATUS_LOOKUP_CODE = APL.LOOKUP_CODE (+) AND APL.LOOKUP_TYPE (+) = 'PCARD TRX STATUS' AND C.CARD_ID=EFL.CARD_ID AND EFD.STATUS_LOOKUP_CODE IN ('VALIDATED', 'DISPUTED', 'PERSONAL', 'REJECTED', 'HOLD')

View Text - HTML Formatted

SELECT EFD.FEED_LINE_ID
, EFD.FEED_DISTRIBUTION_ID
, EFD.INVOICE_ID
, EFD.INVOICE_LINE_ID
, EFD.AMOUNT
, EFD.DIST_CODE_COMBINATION_ID
, EFD.STATUS_CHANGE_DATE
, EFD.STATUS_LOOKUP_CODE
, EFD.INVOICED_FLAG
, EFD.AMOUNT_INCLUDES_TAX_FLAG
, EFD.TAX_CODE
, EFD.MANAGER_APPROVAL_ID
, EFD.EMPLOYEE_VERIFICATION_ID
, EFD.ACCOUNT_SEGMENT_VALUE
, EFD.COST_CENTER
, EFD.ORG_ID
, EFD.LAST_UPDATE_DATE
, EFD.LAST_UPDATED_BY
, EFD.LAST_UPDATE_LOGIN
, EFD.CREATION_DATE
, EFD.CREATED_BY
, EFD.ATTRIBUTE_CATEGORY
, EFD.ATTRIBUTE1
, EFD.ATTRIBUTE2
, EFD.ATTRIBUTE3
, EFD.ATTRIBUTE4
, EFD.ATTRIBUTE5
, EFD.ATTRIBUTE6
, EFD.ATTRIBUTE7
, EFD.ATTRIBUTE8
, EFD.ATTRIBUTE9
, EFD.ATTRIBUTE10
, EFD.ATTRIBUTE11
, EFD.ATTRIBUTE12
, EFD.ATTRIBUTE13
, EFD.ATTRIBUTE14
, EFD.ATTRIBUTE15
, EFL.EMPLOYEE_ID
, EFL.CARD_ID
, EFL.CARD_PROGRAM_ID
, IBY.CARD_NUMBER
, EFL.REFERENCE_NUMBER
, EFL.ALT_REFERENCE_NUMBER
, EFL.CUSTOMER_CODE
, EFL.AMOUNT LINE_AMOUNT
, EFL.ORIGINAL_CURRENCY_AMOUNT
, EFL.ORIGINAL_CURRENCY_CODE
, EFL.POSTED_CURRENCY_CODE
, EFL.CURRENCY_CONVERSION_RATE
, EFL.TRANSACTION_DATE
, EFL.POSTED_DATE
, EFL.RECORD_TYPE
, EFL.CREATE_DISTRIBUTION_FLAG
, EFL.MERCHANT_NAME
, EFL.MERCHANT_NUMBER
, EFL.CARD_CODE_VALUE
, EFL.CODE_ID
, EFL.INDUSTRY_CODE1
, EFL.INDUSTRY_CODE2
, EFL.ALT_MERCHANT_NUMBER
, EFL.MERCHANT_ADDRESS1
, EFL.MERCHANT_ADDRESS2
, EFL.MERCHANT_ADDRESS3
, EFL.MERCHANT_ADDRESS4
, EFL.MERCHANT_CITY
, EFL.MERCHANT_PROVINCE_STATE
, EFL.MERCHANT_POSTAL_CODE
, EFL.MERCHANT_COUNTRY
, EFL.MERCHANT_PHONE
, EFL.SHIP_FROM_POSTAL_CODE
, EFL.SHIP_FROM_COUNTRY
, EFL.SHIP_TO_POSTAL_CODE
, EFL.SHIP_TO_COUNTRY
, EFL.MERCHANT_TAX_ID
, EFL.TAX_PAID_FLAG
, EFL.TAX_AMOUNT
, EFL.TAX_RATE
, EFL.TAX_TYPE
, EFL.ALT_MERCHANT_TAX_ID
, EFL.ALT_TAX_PAID_FLAG
, EFL.ALT_TAX_AMOUNT
, EFL.ALT_TAX_RATE
, EFL.ALT_TAX_TYPE
, EFL.FREIGHT_AMOUNT
, EFL.DUTY_AMOUNT
, EFL.MERCHANT_BUSINESS_TYPE
, EFL.MERCHANT_OWNER_TYPE
, EFL.PRODUCT_CODE
, EFL.ITEM_DESCRIPTION
, EFL.ITEM_QUANTITY
, EFL.ITEM_UOM
, EFL.EXTENDED_ITEM_AMOUNT
, EFL.DISCOUNT_INDICATOR
, EFL.DISCOUNT_AMOUNT
, EFL.EMPLOYEE_VERIFICATION_ID LINE_EMP_VERIFICATION_ID
, EFL.DESCRIPTION LINE_DESCRIPTION
, EFL.REJECT_CODE
, EFL.ATTRIBUTE_CATEGORY LINE_ATTRIBUTE_CATEGORY
, EFL.ATTRIBUTE1 LINE_ATTRIBUTE1
, EFL.ATTRIBUTE2 LINE_ATTRIBUTE2
, EFL.ATTRIBUTE3 LINE_ATTRIBUTE3
, EFL.ATTRIBUTE4 LINE_ATTRIBUTE4
, EFL.ATTRIBUTE5 LINE_ATTRIBUTE5
, EFL.ATTRIBUTE6 LINE_ATTRIBUTE6
, EFL.ATTRIBUTE7 LINE_ATTRIBUTE7
, EFL.ATTRIBUTE8 LINE_ATTRIBUTE8
, EFL.ATTRIBUTE9 LINE_ATTRIBUTE9
, EFL.ATTRIBUTE10 LINE_ATTRIBUTE10
, EFL.ATTRIBUTE11 LINE_ATTRIBUTE11
, EFL.ATTRIBUTE12 LINE_ATTRIBUTE12
, EFL.ATTRIBUTE13 LINE_ATTRIBUTE13
, EFL.ATTRIBUTE14 LINE_ATTRIBUTE14
, EFL.ATTRIBUTE15 LINE_ATTRIBUTE15
, EFL.ATTRIBUTE16 LINE_ATTRIBUTE16
, EFL.ATTRIBUTE17 LINE_ATTRIBUTE17
, EFL.ATTRIBUTE18 LINE_ATTRIBUTE18
, EFL.ATTRIBUTE19 LINE_ATTRIBUTE19
, EFL.ATTRIBUTE20 LINE_ATTRIBUTE20
, EFL.ATTRIBUTE21 LINE_ATTRIBUTE21
, EFL.ATTRIBUTE22 LINE_ATTRIBUTE22
, EFL.ATTRIBUTE23 LINE_ATTRIBUTE23
, EFL.ATTRIBUTE24 LINE_ATTRIBUTE24
, EFL.ATTRIBUTE25 LINE_ATTRIBUTE25
, EFL.ATTRIBUTE26 LINE_ATTRIBUTE26
, EFL.ATTRIBUTE27 LINE_ATTRIBUTE27
, EFL.ATTRIBUTE28 LINE_ATTRIBUTE28
, EFL.ATTRIBUTE29 LINE_ATTRIBUTE29
, EFL.ATTRIBUTE30 LINE_ATTRIBUTE30
, IBY.CARD_NUMBER SET_UP_CARD_NUMBER
, C.EMPLOYEE_ID CARD_EMPLOYEE_ID
, C.DEPARTMENT_NAME
, CP.CARD_PROGRAM_NAME
, CP.CARD_TYPE_LOOKUP_CODE
, CPR.PROFILE_NAME
, APL.DISPLAYED_FIELD DIST_STATUS
, HREMP.FULL_NAME FULL_NAME
, EFD.DESCRIPTION
, '[PLSQL_AGENT]AP_CARD_VERIFY_PKG.VERIFY?P_FEED_DIST_ID='|| TO_CHAR(EFD.FEED_DISTRIBUTION_ID)|| '&'||'P_TICKET='|| FND_HTTP_TICKET.CREATE_TICKET('VERIFY PCARD'
, TO_CHAR(EFD.FEED_DISTRIBUTION_ID)
, 86400) VERIFY_URL
, HREMP.SUPERVISOR_ID
, CP.ADMIN_EMPLOYEE_ID PROGRAM_ADMIN_EMPLOYEE_ID
, CPR.ADMIN_EMPLOYEE_ID PROFILE_ADMIN_EMPLOYEE_ID
, TO_CHAR(EFD.AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(EFL.POSTED_CURRENCY_CODE
, 30)) DISPLAYED_AMOUNT
, IBY.CARD_NUMBER MASKED_CC_NUMBER
FROM AP_EXPENSE_FEED_DISTS EFD
, AP_EXPENSE_FEED_LINES EFL
, AP_CARDS C
, AP_CARD_PROGRAMS CP
, AP_CARD_PROFILES CPR
, AP_LOOKUP_CODES APL
, HR_EMPLOYEES_CURRENT_V HREMP
, IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY
WHERE EFL.FEED_LINE_ID = EFD.FEED_LINE_ID
AND EFL.EMPLOYEE_ID = HREMP.EMPLOYEE_ID
AND C.CARD_REFERENCE_ID = IBY.INSTRUMENT_ID
AND EFL.CARD_PROGRAM_ID = CP.CARD_PROGRAM_ID
AND C.PROFILE_ID = CPR.PROFILE_ID
AND EFD.STATUS_LOOKUP_CODE = APL.LOOKUP_CODE (+)
AND APL.LOOKUP_TYPE (+) = 'PCARD TRX STATUS'
AND C.CARD_ID=EFL.CARD_ID
AND EFD.STATUS_LOOKUP_CODE IN ('VALIDATED'
, 'DISPUTED'
, 'PERSONAL'
, 'REJECTED'
, 'HOLD')