[Home] [Help]
SELECT AI.ROWID ROW_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT
, AI.AMOUNT_PAID AMOUNT_PAID
, AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION
, AI.APPROVED_AMOUNT APPROVED_AMOUNT
, AI.ATTRIBUTE1 ATTRIBUTE1
, AI.ATTRIBUTE10 ATTRIBUTE10
, AI.ATTRIBUTE11 ATTRIBUTE11
, AI.ATTRIBUTE12 ATTRIBUTE12
, AI.ATTRIBUTE13 ATTRIBUTE13
, AI.ATTRIBUTE14 ATTRIBUTE14
, AI.ATTRIBUTE15 ATTRIBUTE15
, AI.ATTRIBUTE2 ATTRIBUTE2
, AI.ATTRIBUTE3 ATTRIBUTE3
, AI.ATTRIBUTE4 ATTRIBUTE4
, AI.ATTRIBUTE5 ATTRIBUTE5
, AI.ATTRIBUTE6 ATTRIBUTE6
, AI.ATTRIBUTE7 ATTRIBUTE7
, AI.ATTRIBUTE8 ATTRIBUTE8
, AI.ATTRIBUTE9 ATTRIBUTE9
, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AI.AUTHORIZED_BY AUTHORIZED_BY
, AI.AWT_FLAG AWT_FLAG
, AI.AWT_GROUP_ID AWT_GROUP_ID
, AI.BASE_AMOUNT BASE_AMOUNT
, AI.BATCH_ID BATCH_ID
, AI.CANCELLED_AMOUNT CANCELLED_AMOUNT
, AI.CANCELLED_BY CANCELLED_BY
, AI.CANCELLED_DATE CANCELLED_DATE
, AI.CREATED_BY CREATED_BY
, AI.CREATION_DATE CREATION_DATE
, AI.DESCRIPTION DESCRIPTION
, AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN
, AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE
, AI.EXCHANGE_DATE EXCHANGE_DATE
, AI.EXCHANGE_RATE EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG
, AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, AI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE
, AI.INVOICE_AMOUNT INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT
, AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL
, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG
, AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE
, AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID
, AI.PA_QUANTITY PA_QUANTITY
, AI.PO_HEADER_ID PO_HEADER_ID
, AI.PROJECT_ID PROJECT_ID
, AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AI.SOURCE SOURCE
, AI.TASK_ID TASK_ID
, AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT
, AI.TERMS_DATE TERMS_DATE
, AI.TERMS_ID TERMS_ID
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_PREPAY_AMOUNT VENDOR_PREPAY_AMOUNT
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.VOUCHER_NUM VOUCHER_NUM
, AB.BATCH_NAME BATCH_NAME
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT',
DECODE(AI.EARLIEST_SETTLEMENT_DATE, '','PERMANENT', 'TEMPORARY'))
PREPAYMENT_TYPE_LOOKUP_CODE
, GDCT.USER_CONVERSION_TYPE USER_RATE_TYPE
, ARP.RECURRING_PAY_NUM RECURRING_PAY_NUM
, ARP.REC_PAY_PERIOD_TYPE RECURRING_PERIOD_TYPE
, AOPT.DESCRIPTION RECURRING_PAYMENT_TYPE
, AT.NAME TERMS_NAME
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT',
AI.ORIGINAL_PREPAYMENT_AMOUNT, AI.INVOICE_AMOUNT)
ACTUAL_INVOICE_AMOUNT
, FDS.NAME DOC_SEQUENCE_NAME
, FDSC.NAME DOC_CATEGORY_NAME
, GSOB.NAME SET_OF_BOOKS_NAME
, HOU.NAME EXPENDITURE_ORGANIZATION_NAME
, HP.PARTY_NAME VENDOR_NAME
, DECODE(AI.Invoice_Type_Lookup_Code, 'PAYMENT REQUEST', NULL,
PV.SEGMENT1) VENDOR_NUMBER
, PV.NUM_1099 NUM_1099
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PVS.HOLD_FUTURE_PAYMENTS_FLAG HOLD_FUTURE_PAYMENTS_FLAG
, PVS.INVOICE_AMOUNT_LIMIT INVOICE_AMOUNT_LIMIT
, PVS.TERMS_DATE_BASIS TERMS_DATE_BASIS
, NVL(AB.PAYMENT_PRIORITY, PVS.PAYMENT_PRIORITY) PAYMENT_PRIORITY
, PVS.PAY_DATE_BASIS_LOOKUP_CODE PAY_DATE_BASIS_LOOKUP_CODE
, PVS.ALWAYS_TAKE_DISC_FLAG ALWAYS_TAKE_DISC_FLAG
, AI.EXCLUDE_FREIGHT_FROM_DISCOUNT EXCLUDE_FREIGHT_FROM_DISCOUNT
, PVS.ALLOW_AWT_FLAG SITE_ALLOW_AWT_FLAG
, DECODE(PV.TYPE_1099, '','', DECODE(ASP.COMBINED_FILING_FLAG, 'N',
'', DECODE(ASP.INCOME_TAX_REGION_FLAG, 'Y',
DECODE(PVS.country,
'US', PVS.state,
NULL),
ASP.INCOME_TAX_REGION))) INCOME_TAX_REGION
, NVL(AP_INVOICES_PKG.GET_DISTRIBUTION_TOTAL( AI.INVOICE_ID),0)
DISTRIBUTION_TOTAL
, AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID) POSTING_FLAG
, AP_INVOICES_PKG.GET_APPROVAL_STATUS( AI.INVOICE_ID,
AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG,
AI.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE
, AP_INVOICES_PKG.GET_PO_NUMBER( AI.INVOICE_ID) PO_NUMBER
, AP_INVOICES_PKG.GET_RELEASE_NUMBER(AI.INVOICE_ID)
, AP_INVOICES_PKG.GET_RECEIPT_NUMBER(AI.INVOICE_ID)
, AP_INVOICES_PKG.GET_AMOUNT_WITHHELD( AI.INVOICE_ID)
AMOUNT_WITHHELD
, AP_PREPAY_UTILS_PKG.GET_PREPAID_AMOUNT( AI.INVOICE_ID)
PREPAID_AMOUNT
, AP_INVOICES_PKG.GET_NOTES_COUNT( AI.INVOICE_ID) NOTES_COUNT
, AP_INVOICES_PKG.GET_HOLDS_COUNT( AI.INVOICE_ID) HOLDS_COUNT
, AP_INVOICES_PKG.GET_AMOUNT_HOLD_FLAG( AI.INVOICE_ID)
AMOUNT_HOLD_FLAG
, AP_INVOICES_PKG.GET_VENDOR_HOLD_FLAG( AI.INVOICE_ID)
VENDOR_HOLD_FLAG
, AP_INVOICES_PKG.GET_ENCUMBERED_FLAG( AI.INVOICE_ID)
ENCUMBERED_FLAG
, AI.GL_DATE GL_DATE
, AP_INVOICES_PKG.GET_PERIOD_NAME( AI.GL_DATE, NULL, AI.ORG_ID) PERIOD_NAME
, AP_INVOICES_PKG.SELECTED_FOR_PAYMENT_FLAG( AI.INVOICE_ID)
SELECTED_FOR_PAYMENT_FLAG
, AP_INVOICES_PKG.GET_UNPOSTED_VOID_PAYMENT ( AI.INVOICE_ID)
UNPOSTED_VOID_PAYMENT_FLAG
, AP_INVOICES_PKG.GET_DISCOUNT_PAY_DISTS_FLAG ( AI.INVOICE_ID)
DISCOUNT_PAY_DISTS_FLAG
, AP_PREPAY_UTILS_PKG.GET_PREPAYMENTS_APPLIED_FLAG ( AI.INVOICE_ID)
PREPAYMENTS_APPLIED_FLAG
, AP_INVOICES_PKG.GET_PAYMENTS_EXIST_FLAG ( AI.INVOICE_ID)
PAYMENTS_EXIST_FLAG
, AP_INVOICES_PKG.GET_PACKET_ID ( AI.INVOICE_ID) QUERY_PACKET_ID
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT',
AP_PREPAY_UTILS_PKG.GET_PREPAY_AMOUNT_APPLIED ( AI.INVOICE_ID), 0)
PREPAY_AMOUNT_APPLIED
, FND_USER_AP_PKG.GET_USER_NAME( AI.CANCELLED_BY)
CANCELLED_BY_DISPLAY
, AI.APPROVAL_STATUS APPROVAL_STATUS
, AI.POSTING_STATUS POSTING_STATUS
, AI.REFERENCE_1 REFERENCE_1
, AI.REFERENCE_2 REFERENCE_2
, AI.ORG_ID ORG_ID
, AWT.NAME AWT_GROUP_NAME
, PAP.SEGMENT1 PROJECT
, PAT.TASK_NUMBER TASK
, FC.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION PRECISION
, AI.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AI.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AI.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AI.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AI.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AI.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AI.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AI.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AI.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AI.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AI.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AI.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AI.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AI.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AI.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AI.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AI.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AI.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AI.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AI.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AI.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, AI.PAYMENT_CROSS_RATE_TYPE
, AI.PAYMENT_CROSS_RATE_DATE
, NVL(AI.PAY_CURR_INVOICE_AMOUNT,AI.INVOICE_AMOUNT)
, PV.TYPE_1099
, AI.AWARD_ID
, AI.WFAPPROVAL_STATUS
, AI.APPROVAL_READY_FLAG
, AI.APPROVAL_ITERATION
, ALC4.DISPLAYED_FIELD WFAPPROVAL_STATUS_DSP
, AI.REQUESTER_ID
, AI.QUICK_CREDIT QUICK_CREDIT
, AI.CREDITED_INVOICE_ID CREDITED_INVOICE_ID
, DECODE(AI.CREDITED_INVOICE_ID,NULL,NULL,
AP_INVOICES_UTILITY_PKG.GET_INVOICE_NUM(
AI.CREDITED_INVOICE_ID)) CREDITED_INVOICE_NUM
, AI.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, DSET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
, NVL(AP_INVOICES_UTILITY_PKG.GET_LINE_TOTAL(AI.INVOICE_ID),0)
LINE_TOTAL
, AP_INVOICES_UTILITY_PKG.GET_MAX_INV_LINE_NUM(AI.INVOICE_ID) HIGHEST_LINE_NUM
/*Etax: Invwkb*/
,AI.TOTAL_TAX_AMOUNT
,AI.CONTROL_AMOUNT
,AI.TAXATION_COUNTRY
,FND.TERRITORY_SHORT_NAME
,AI.TRX_BUSINESS_CATEGORY
,ZBC.CLASSIFICATION_NAME TRX_BUSINESS_CATEGORY_NAME
,AI.USER_DEFINED_FISC_CLASS
,AI.TAX_RELATED_INVOICE_ID
,DECODE(AI.TAX_RELATED_INVOICE_ID,NULL,NULL,
AP_INVOICES_UTILITY_PKG.GET_INVOICE_NUM(
AI.TAX_RELATED_INVOICE_ID)) TAX_RELATED_INVOICE_NUM
,AI.DOCUMENT_SUB_TYPE
,AI.SELF_ASSESSED_TAX_AMOUNT
,AI.TAX_INVOICE_INTERNAL_SEQ
,AI.SUPPLIER_TAX_INVOICE_NUMBER
,AI.TAX_INVOICE_RECORDING_DATE
,AI.SUPPLIER_TAX_INVOICE_DATE
,AI.SUPPLIER_TAX_EXCHANGE_RATE
,AI.LEGAL_ENTITY_ID
,AI.FORCE_REVALIDATION_FLAG
,AI.APPLICATION_ID
,AI.PRODUCT_TABLE
,AI.REFERENCE_KEY1
,AI.REFERENCE_KEY2
,AI.REFERENCE_KEY3
,AI.REFERENCE_KEY4
,AI.REFERENCE_KEY5
,AI.DISC_IS_INV_LESS_TAX_FLAG
,AI.QUICK_PO_HEADER_ID
,PH.SEGMENT1 QUICK_PO_NUMBER
,AI.PAYMENT_METHOD_CODE
,IBY1.PAYMENT_METHOD_NAME
,AI.PAYMENT_REASON_CODE
,IBY2.meaning
,AI.PAYMENT_REASON_COMMENTS
,AI.UNIQUE_REMITTANCE_IDENTIFIER
,AI.URI_CHECK_DIGIT
,AI.REMITTANCE_MESSAGE1
,AI.REMITTANCE_MESSAGE2
,AI.REMITTANCE_MESSAGE3
,AI.BANK_CHARGE_BEARER
,IBY3.MEANING
,AI.DELIVERY_CHANNEL_CODE
,IBY4.MEANING
,AI.SETTLEMENT_PRIORITY
,IBY5.MEANING
,nvl(ai.party_id,PV.PARTY_ID)
,ai.party_site_id /* Bug 5362316 */
,AI.NET_OF_RETAINAGE_FLAG
,AI.RELEASE_AMOUNT_NET_OF_TAX
,NULL RETAINED_TOTAL
,NULL ITEM_TOTAL
,NULL FREIGHT_TOTAL
,NULL MISC_TOTAL
,NULL PREPAY_APP_TOTAL
,NULL AWT_TOTAL
,AI.cust_registration_code
,AI.cust_registration_number
,AI.port_of_entry_code
,ai.external_bank_account_id
,ibybnk.bank_account_name
,ibybnk.masked_bank_account_num
,ai.payment_function
,ai.pay_proc_trxn_type_code
,AI.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID
,AWT1.NAME PAY_AWT_GROUP_NAME
,AI.ORIGINAL_INVOICE_AMOUNT /* Bug 7286433 QPDR Project*/
,AI.DISPUTE_REASON /* Bug 7286433 QPDR Project*/
,NVL(AI.REMIT_TO_SUPPLIER_NAME, HP.PARTY_NAME) REMIT_TO_SUPPLIER_NAME /* Bug 7535348 */
,NVL(AI.REMIT_TO_SUPPLIER_ID, AI.VENDOR_ID) REMIT_TO_SUPPLIER_ID
,NVL(AI.REMIT_TO_SUPPLIER_SITE, PVS.VENDOR_SITE_CODE) REMIT_TO_SUPPLIER_SITE
,NVL(AI.REMIT_TO_SUPPLIER_SITE_ID, AI.VENDOR_SITE_ID) REMIT_TO_SUPPLIER_SITE_ID
,NVL(AI.RELATIONSHIP_ID, -1) RELATIONSHIP_ID /* Bug 7535348 */
FROM AP_INVOICES_ALL AI,
AP_BATCHES_ALL AB,
AP_LOOKUP_CODES ALC1,
AP_LOOKUP_CODES ALC2,
AP_LOOKUP_CODES ALC4,
AP_OTHER_PERIOD_TYPES AOPT,
AP_RECURRING_PAYMENTS_ALL ARP,
AP_TERMS AT,
AP_SYSTEM_PARAMETERS ASP,
FND_CURRENCIES FC,
FND_DOCUMENT_SEQUENCES FDS,
FND_DOC_SEQUENCE_CATEGORIES FDSC,
GL_SETS_OF_BOOKS GSOB,
GL_DAILY_CONVERSION_TYPES GDCT,
HR_ORGANIZATION_UNITS HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
AP_AWT_GROUPS AWT,
AP_AWT_GROUPS AWT1,
PA_PROJECTS_ALL PAP,
PA_TASKS PAT,
ZX_FC_BUSINESS_CATEGORIES_V ZBC,
FND_TERRITORIES_TL FND,
AP_DISTRIBUTION_SETS DSET,
PO_HEADERS PH,
iby_payment_methods_vl iby1,
iby_payment_reasons_vl iby2,
fnd_lookups iby3,
iby_delivery_channels_vl iby4,
fnd_lookups iby5,
iby_ext_bank_accounts ibybnk,
HZ_PARTIES HP
WHERE AI.BATCH_ID = AB.BATCH_ID (+)
AND AI.RECURRING_PAYMENT_ID = ARP.RECURRING_PAYMENT_ID (+)
AND ARP.REC_PAY_PERIOD_TYPE = AOPT.PERIOD_TYPE (+)
AND AOPT.MODULE (+) = 'RECURRING PAYMENT'
AND AI.TERMS_ID = AT.TERM_ID (+)
AND ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG
AND ALC4.LOOKUP_TYPE (+) = 'AP_WFAPPROVAL_STATUS'
AND ALC4.LOOKUP_CODE (+) = AI.WFAPPROVAL_STATUS
AND ZBC.CLASSIFICATION_CODE(+) = AI.TRX_BUSINESS_CATEGORY
AND ZBC.APPLICATION_ID(+) = 200
AND ZBC.ENTITY_CODE(+) = 'AP_INVOICES'
AND AI.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID (+)
AND FDSC.CODE (+) = AI.DOC_CATEGORY_CODE
AND FDSC.APPLICATION_ID (+) = 200
AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AI.EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND AI.PROJECT_ID = PAP.PROJECT_ID (+)
AND AI.TASK_ID = PAT.TASK_ID (+)
AND AI.AWT_GROUP_ID = AWT.GROUP_ID (+)
AND AI.PAY_AWT_GROUP_ID = AWT1.GROUP_ID (+)
AND AI.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE (+)
AND AI.ORG_ID = ASP.ORG_ID
AND FND.territory_code(+) = AI.taxation_country
AND (AI.TAXATION_COUNTRY IS NULL OR
FND.LANGUAGE = USERENV('LANG'))
AND AI.DISTRIBUTION_SET_ID = DSET.DISTRIBUTION_SET_ID(+)
AND AI.QUICK_PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND AI.PAYMENT_METHOD_CODE = IBY1.PAYMENT_METHOD_CODE (+)
AND AI.PAYMENT_REASON_CODE = IBY2.PAYMENT_REASON_CODE (+)
AND AI.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+)
AND IBY3.LOOKUP_TYPE (+) = 'IBY_BANK_CHARGE_BEARER'
AND AI.DELIVERY_CHANNEL_CODE = IBY4.DELIVERY_CHANNEL_CODE(+)
AND AI.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE (+)
AND IBY5.LOOKUP_TYPE (+) = 'IBY_SETTLEMENT_PRIORITY'
AND IBYBNK.EXT_BANK_ACCOUNT_ID (+) = AI.EXTERNAL_BANK_ACCOUNT_ID
AND AI.PARTY_ID = HP.PARTY_ID
/* AND HP.PARTY_ID = PV.PARTY_ID (+) bug:7366363 */
/*Added below line from ap_invoices_v.xdf 120.5
to include change done in to this SQL*/
AND PV.VENDOR_ID(+) = AI.VENDOR_ID /* bug:7366363 */
AND AI.APPROVAL_READY_FLAG <> 'S'
SELECT AI.ROWID ROW_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AI.AMOUNT_APPLICABLE_TO_DISCOUNT AMOUNT_APPLICABLE_TO_DISCOUNT
, AI.AMOUNT_PAID AMOUNT_PAID
, AI.APPROVAL_DESCRIPTION APPROVAL_DESCRIPTION
, AI.APPROVED_AMOUNT APPROVED_AMOUNT
, AI.ATTRIBUTE1 ATTRIBUTE1
, AI.ATTRIBUTE10 ATTRIBUTE10
, AI.ATTRIBUTE11 ATTRIBUTE11
, AI.ATTRIBUTE12 ATTRIBUTE12
, AI.ATTRIBUTE13 ATTRIBUTE13
, AI.ATTRIBUTE14 ATTRIBUTE14
, AI.ATTRIBUTE15 ATTRIBUTE15
, AI.ATTRIBUTE2 ATTRIBUTE2
, AI.ATTRIBUTE3 ATTRIBUTE3
, AI.ATTRIBUTE4 ATTRIBUTE4
, AI.ATTRIBUTE5 ATTRIBUTE5
, AI.ATTRIBUTE6 ATTRIBUTE6
, AI.ATTRIBUTE7 ATTRIBUTE7
, AI.ATTRIBUTE8 ATTRIBUTE8
, AI.ATTRIBUTE9 ATTRIBUTE9
, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AI.AUTHORIZED_BY AUTHORIZED_BY
, AI.AWT_FLAG AWT_FLAG
, AI.AWT_GROUP_ID AWT_GROUP_ID
, AI.BASE_AMOUNT BASE_AMOUNT
, AI.BATCH_ID BATCH_ID
, AI.CANCELLED_AMOUNT CANCELLED_AMOUNT
, AI.CANCELLED_BY CANCELLED_BY
, AI.CANCELLED_DATE CANCELLED_DATE
, AI.CREATED_BY CREATED_BY
, AI.CREATION_DATE CREATION_DATE
, AI.DESCRIPTION DESCRIPTION
, AI.DISCOUNT_AMOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN
, AI.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AI.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE
, AI.EXCHANGE_DATE EXCHANGE_DATE
, AI.EXCHANGE_RATE EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AI.EXCLUSIVE_PAYMENT_FLAG EXCLUSIVE_PAYMENT_FLAG
, AI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, AI.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, AI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, AI.GOODS_RECEIVED_DATE GOODS_RECEIVED_DATE
, AI.INVOICE_AMOUNT INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.INVOICE_RECEIVED_DATE INVOICE_RECEIVED_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AI.ORIGINAL_PREPAYMENT_AMOUNT ORIGINAL_PREPAYMENT_AMOUNT
, AI.PAYMENT_AMOUNT_TOTAL PAYMENT_AMOUNT_TOTAL
, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG
, AI.PAY_GROUP_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE
, AI.PA_DEFAULT_DIST_CCID PA_DEFAULT_DIST_CCID
, AI.PA_QUANTITY PA_QUANTITY
, AI.PO_HEADER_ID PO_HEADER_ID
, AI.PROJECT_ID PROJECT_ID
, AI.RECURRING_PAYMENT_ID RECURRING_PAYMENT_ID
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AI.SOURCE SOURCE
, AI.TASK_ID TASK_ID
, AI.TEMP_CANCELLED_AMOUNT TEMP_CANCELLED_AMOUNT
, AI.TERMS_DATE TERMS_DATE
, AI.TERMS_ID TERMS_ID
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_PREPAY_AMOUNT VENDOR_PREPAY_AMOUNT
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.VOUCHER_NUM VOUCHER_NUM
, AB.BATCH_NAME BATCH_NAME
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
,
DECODE(AI.EARLIEST_SETTLEMENT_DATE
, ''
, 'PERMANENT'
, 'TEMPORARY'))
PREPAYMENT_TYPE_LOOKUP_CODE
, GDCT.USER_CONVERSION_TYPE USER_RATE_TYPE
, ARP.RECURRING_PAY_NUM RECURRING_PAY_NUM
, ARP.REC_PAY_PERIOD_TYPE RECURRING_PERIOD_TYPE
, AOPT.DESCRIPTION RECURRING_PAYMENT_TYPE
, AT.NAME TERMS_NAME
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
,
AI.ORIGINAL_PREPAYMENT_AMOUNT
, AI.INVOICE_AMOUNT)
ACTUAL_INVOICE_AMOUNT
, FDS.NAME DOC_SEQUENCE_NAME
, FDSC.NAME DOC_CATEGORY_NAME
, GSOB.NAME SET_OF_BOOKS_NAME
, HOU.NAME EXPENDITURE_ORGANIZATION_NAME
, HP.PARTY_NAME VENDOR_NAME
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PAYMENT REQUEST'
, NULL
,
PV.SEGMENT1) VENDOR_NUMBER
, PV.NUM_1099 NUM_1099
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PVS.HOLD_FUTURE_PAYMENTS_FLAG HOLD_FUTURE_PAYMENTS_FLAG
, PVS.INVOICE_AMOUNT_LIMIT INVOICE_AMOUNT_LIMIT
, PVS.TERMS_DATE_BASIS TERMS_DATE_BASIS
, NVL(AB.PAYMENT_PRIORITY
, PVS.PAYMENT_PRIORITY) PAYMENT_PRIORITY
, PVS.PAY_DATE_BASIS_LOOKUP_CODE PAY_DATE_BASIS_LOOKUP_CODE
, PVS.ALWAYS_TAKE_DISC_FLAG ALWAYS_TAKE_DISC_FLAG
, AI.EXCLUDE_FREIGHT_FROM_DISCOUNT EXCLUDE_FREIGHT_FROM_DISCOUNT
, PVS.ALLOW_AWT_FLAG SITE_ALLOW_AWT_FLAG
, DECODE(PV.TYPE_1099
, ''
, ''
, DECODE(ASP.COMBINED_FILING_FLAG
, 'N'
,
''
, DECODE(ASP.INCOME_TAX_REGION_FLAG
, 'Y'
,
DECODE(PVS.COUNTRY
,
'US'
, PVS.STATE
,
NULL)
,
ASP.INCOME_TAX_REGION))) INCOME_TAX_REGION
, NVL(AP_INVOICES_PKG.GET_DISTRIBUTION_TOTAL( AI.INVOICE_ID)
, 0)
DISTRIBUTION_TOTAL
, AP_INVOICES_PKG.GET_POSTING_STATUS( AI.INVOICE_ID) POSTING_FLAG
, AP_INVOICES_PKG.GET_APPROVAL_STATUS( AI.INVOICE_ID
,
AI.INVOICE_AMOUNT
, AI.PAYMENT_STATUS_FLAG
,
AI.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE
, AP_INVOICES_PKG.GET_PO_NUMBER( AI.INVOICE_ID) PO_NUMBER
, AP_INVOICES_PKG.GET_RELEASE_NUMBER(AI.INVOICE_ID)
, AP_INVOICES_PKG.GET_RECEIPT_NUMBER(AI.INVOICE_ID)
, AP_INVOICES_PKG.GET_AMOUNT_WITHHELD( AI.INVOICE_ID)
AMOUNT_WITHHELD
, AP_PREPAY_UTILS_PKG.GET_PREPAID_AMOUNT( AI.INVOICE_ID)
PREPAID_AMOUNT
, AP_INVOICES_PKG.GET_NOTES_COUNT( AI.INVOICE_ID) NOTES_COUNT
, AP_INVOICES_PKG.GET_HOLDS_COUNT( AI.INVOICE_ID) HOLDS_COUNT
, AP_INVOICES_PKG.GET_AMOUNT_HOLD_FLAG( AI.INVOICE_ID)
AMOUNT_HOLD_FLAG
, AP_INVOICES_PKG.GET_VENDOR_HOLD_FLAG( AI.INVOICE_ID)
VENDOR_HOLD_FLAG
, AP_INVOICES_PKG.GET_ENCUMBERED_FLAG( AI.INVOICE_ID)
ENCUMBERED_FLAG
, AI.GL_DATE GL_DATE
, AP_INVOICES_PKG.GET_PERIOD_NAME( AI.GL_DATE
, NULL
, AI.ORG_ID) PERIOD_NAME
, AP_INVOICES_PKG.SELECTED_FOR_PAYMENT_FLAG( AI.INVOICE_ID)
SELECTED_FOR_PAYMENT_FLAG
, AP_INVOICES_PKG.GET_UNPOSTED_VOID_PAYMENT ( AI.INVOICE_ID)
UNPOSTED_VOID_PAYMENT_FLAG
, AP_INVOICES_PKG.GET_DISCOUNT_PAY_DISTS_FLAG ( AI.INVOICE_ID)
DISCOUNT_PAY_DISTS_FLAG
, AP_PREPAY_UTILS_PKG.GET_PREPAYMENTS_APPLIED_FLAG ( AI.INVOICE_ID)
PREPAYMENTS_APPLIED_FLAG
, AP_INVOICES_PKG.GET_PAYMENTS_EXIST_FLAG ( AI.INVOICE_ID)
PAYMENTS_EXIST_FLAG
, AP_INVOICES_PKG.GET_PACKET_ID ( AI.INVOICE_ID) QUERY_PACKET_ID
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
,
AP_PREPAY_UTILS_PKG.GET_PREPAY_AMOUNT_APPLIED ( AI.INVOICE_ID)
, 0)
PREPAY_AMOUNT_APPLIED
, FND_USER_AP_PKG.GET_USER_NAME( AI.CANCELLED_BY)
CANCELLED_BY_DISPLAY
, AI.APPROVAL_STATUS APPROVAL_STATUS
, AI.POSTING_STATUS POSTING_STATUS
, AI.REFERENCE_1 REFERENCE_1
, AI.REFERENCE_2 REFERENCE_2
, AI.ORG_ID ORG_ID
, AWT.NAME AWT_GROUP_NAME
, PAP.SEGMENT1 PROJECT
, PAT.TASK_NUMBER TASK
, FC.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION PRECISION
, AI.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AI.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AI.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AI.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AI.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AI.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AI.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AI.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AI.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AI.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AI.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AI.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AI.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AI.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AI.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AI.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AI.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AI.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AI.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AI.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AI.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, AI.PAYMENT_CROSS_RATE_TYPE
, AI.PAYMENT_CROSS_RATE_DATE
, NVL(AI.PAY_CURR_INVOICE_AMOUNT
, AI.INVOICE_AMOUNT)
, PV.TYPE_1099
, AI.AWARD_ID
, AI.WFAPPROVAL_STATUS
, AI.APPROVAL_READY_FLAG
, AI.APPROVAL_ITERATION
, ALC4.DISPLAYED_FIELD WFAPPROVAL_STATUS_DSP
, AI.REQUESTER_ID
, AI.QUICK_CREDIT QUICK_CREDIT
, AI.CREDITED_INVOICE_ID CREDITED_INVOICE_ID
, DECODE(AI.CREDITED_INVOICE_ID
, NULL
, NULL
,
AP_INVOICES_UTILITY_PKG.GET_INVOICE_NUM(
AI.CREDITED_INVOICE_ID)) CREDITED_INVOICE_NUM
, AI.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, DSET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
, NVL(AP_INVOICES_UTILITY_PKG.GET_LINE_TOTAL(AI.INVOICE_ID)
, 0)
LINE_TOTAL
, AP_INVOICES_UTILITY_PKG.GET_MAX_INV_LINE_NUM(AI.INVOICE_ID) HIGHEST_LINE_NUM
/*ETAX: INVWKB*/
, AI.TOTAL_TAX_AMOUNT
, AI.CONTROL_AMOUNT
, AI.TAXATION_COUNTRY
, FND.TERRITORY_SHORT_NAME
, AI.TRX_BUSINESS_CATEGORY
, ZBC.CLASSIFICATION_NAME TRX_BUSINESS_CATEGORY_NAME
, AI.USER_DEFINED_FISC_CLASS
, AI.TAX_RELATED_INVOICE_ID
, DECODE(AI.TAX_RELATED_INVOICE_ID
, NULL
, NULL
,
AP_INVOICES_UTILITY_PKG.GET_INVOICE_NUM(
AI.TAX_RELATED_INVOICE_ID)) TAX_RELATED_INVOICE_NUM
, AI.DOCUMENT_SUB_TYPE
, AI.SELF_ASSESSED_TAX_AMOUNT
, AI.TAX_INVOICE_INTERNAL_SEQ
, AI.SUPPLIER_TAX_INVOICE_NUMBER
, AI.TAX_INVOICE_RECORDING_DATE
, AI.SUPPLIER_TAX_INVOICE_DATE
, AI.SUPPLIER_TAX_EXCHANGE_RATE
, AI.LEGAL_ENTITY_ID
, AI.FORCE_REVALIDATION_FLAG
, AI.APPLICATION_ID
, AI.PRODUCT_TABLE
, AI.REFERENCE_KEY1
, AI.REFERENCE_KEY2
, AI.REFERENCE_KEY3
, AI.REFERENCE_KEY4
, AI.REFERENCE_KEY5
, AI.DISC_IS_INV_LESS_TAX_FLAG
, AI.QUICK_PO_HEADER_ID
, PH.SEGMENT1 QUICK_PO_NUMBER
, AI.PAYMENT_METHOD_CODE
, IBY1.PAYMENT_METHOD_NAME
, AI.PAYMENT_REASON_CODE
, IBY2.MEANING
, AI.PAYMENT_REASON_COMMENTS
, AI.UNIQUE_REMITTANCE_IDENTIFIER
, AI.URI_CHECK_DIGIT
, AI.REMITTANCE_MESSAGE1
, AI.REMITTANCE_MESSAGE2
, AI.REMITTANCE_MESSAGE3
, AI.BANK_CHARGE_BEARER
, IBY3.MEANING
, AI.DELIVERY_CHANNEL_CODE
, IBY4.MEANING
, AI.SETTLEMENT_PRIORITY
, IBY5.MEANING
, NVL(AI.PARTY_ID
, PV.PARTY_ID)
, AI.PARTY_SITE_ID /* BUG 5362316 */
, AI.NET_OF_RETAINAGE_FLAG
, AI.RELEASE_AMOUNT_NET_OF_TAX
, NULL RETAINED_TOTAL
, NULL ITEM_TOTAL
, NULL FREIGHT_TOTAL
, NULL MISC_TOTAL
, NULL PREPAY_APP_TOTAL
, NULL AWT_TOTAL
, AI.CUST_REGISTRATION_CODE
, AI.CUST_REGISTRATION_NUMBER
, AI.PORT_OF_ENTRY_CODE
, AI.EXTERNAL_BANK_ACCOUNT_ID
, IBYBNK.BANK_ACCOUNT_NAME
, IBYBNK.MASKED_BANK_ACCOUNT_NUM
, AI.PAYMENT_FUNCTION
, AI.PAY_PROC_TRXN_TYPE_CODE
, AI.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID
, AWT1.NAME PAY_AWT_GROUP_NAME
, AI.ORIGINAL_INVOICE_AMOUNT /* BUG 7286433 QPDR PROJECT*/
, AI.DISPUTE_REASON /* BUG 7286433 QPDR PROJECT*/
, NVL(AI.REMIT_TO_SUPPLIER_NAME
, HP.PARTY_NAME) REMIT_TO_SUPPLIER_NAME /* BUG 7535348 */
, NVL(AI.REMIT_TO_SUPPLIER_ID
, AI.VENDOR_ID) REMIT_TO_SUPPLIER_ID
, NVL(AI.REMIT_TO_SUPPLIER_SITE
, PVS.VENDOR_SITE_CODE) REMIT_TO_SUPPLIER_SITE
, NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID) REMIT_TO_SUPPLIER_SITE_ID
, NVL(AI.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID /* BUG 7535348 */
FROM AP_INVOICES_ALL AI
,
AP_BATCHES_ALL AB
,
AP_LOOKUP_CODES ALC1
,
AP_LOOKUP_CODES ALC2
,
AP_LOOKUP_CODES ALC4
,
AP_OTHER_PERIOD_TYPES AOPT
,
AP_RECURRING_PAYMENTS_ALL ARP
,
AP_TERMS AT
,
AP_SYSTEM_PARAMETERS ASP
,
FND_CURRENCIES FC
,
FND_DOCUMENT_SEQUENCES FDS
,
FND_DOC_SEQUENCE_CATEGORIES FDSC
,
GL_SETS_OF_BOOKS GSOB
,
GL_DAILY_CONVERSION_TYPES GDCT
,
HR_ORGANIZATION_UNITS HOU
,
PO_VENDORS PV
,
PO_VENDOR_SITES_ALL PVS
,
AP_AWT_GROUPS AWT
,
AP_AWT_GROUPS AWT1
,
PA_PROJECTS_ALL PAP
,
PA_TASKS PAT
,
ZX_FC_BUSINESS_CATEGORIES_V ZBC
,
FND_TERRITORIES_TL FND
,
AP_DISTRIBUTION_SETS DSET
,
PO_HEADERS PH
,
IBY_PAYMENT_METHODS_VL IBY1
,
IBY_PAYMENT_REASONS_VL IBY2
,
FND_LOOKUPS IBY3
,
IBY_DELIVERY_CHANNELS_VL IBY4
,
FND_LOOKUPS IBY5
,
IBY_EXT_BANK_ACCOUNTS IBYBNK
,
HZ_PARTIES HP
WHERE AI.BATCH_ID = AB.BATCH_ID (+)
AND AI.RECURRING_PAYMENT_ID = ARP.RECURRING_PAYMENT_ID (+)
AND ARP.REC_PAY_PERIOD_TYPE = AOPT.PERIOD_TYPE (+)
AND AOPT.MODULE (+) = 'RECURRING PAYMENT'
AND AI.TERMS_ID = AT.TERM_ID (+)
AND ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG
AND ALC4.LOOKUP_TYPE (+) = 'AP_WFAPPROVAL_STATUS'
AND ALC4.LOOKUP_CODE (+) = AI.WFAPPROVAL_STATUS
AND ZBC.CLASSIFICATION_CODE(+) = AI.TRX_BUSINESS_CATEGORY
AND ZBC.APPLICATION_ID(+) = 200
AND ZBC.ENTITY_CODE(+) = 'AP_INVOICES'
AND AI.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID (+)
AND FDSC.CODE (+) = AI.DOC_CATEGORY_CODE
AND FDSC.APPLICATION_ID (+) = 200
AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AI.EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND AI.PROJECT_ID = PAP.PROJECT_ID (+)
AND AI.TASK_ID = PAT.TASK_ID (+)
AND AI.AWT_GROUP_ID = AWT.GROUP_ID (+)
AND AI.PAY_AWT_GROUP_ID = AWT1.GROUP_ID (+)
AND AI.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE (+)
AND AI.ORG_ID = ASP.ORG_ID
AND FND.TERRITORY_CODE(+) = AI.TAXATION_COUNTRY
AND (AI.TAXATION_COUNTRY IS NULL OR
FND.LANGUAGE = USERENV('LANG'))
AND AI.DISTRIBUTION_SET_ID = DSET.DISTRIBUTION_SET_ID(+)
AND AI.QUICK_PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND AI.PAYMENT_METHOD_CODE = IBY1.PAYMENT_METHOD_CODE (+)
AND AI.PAYMENT_REASON_CODE = IBY2.PAYMENT_REASON_CODE (+)
AND AI.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+)
AND IBY3.LOOKUP_TYPE (+) = 'IBY_BANK_CHARGE_BEARER'
AND AI.DELIVERY_CHANNEL_CODE = IBY4.DELIVERY_CHANNEL_CODE(+)
AND AI.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE (+)
AND IBY5.LOOKUP_TYPE (+) = 'IBY_SETTLEMENT_PRIORITY'
AND IBYBNK.EXT_BANK_ACCOUNT_ID (+) = AI.EXTERNAL_BANK_ACCOUNT_ID
AND AI.PARTY_ID = HP.PARTY_ID
/*
AND HP.PARTY_ID = PV.PARTY_ID (+) BUG:7366363 */
/*ADDED BELOW LINE
FROM AP_INVOICES_V.XDF 120.5
TO INCLUDE CHANGE DONE IN TO THIS SQL*/
AND PV.VENDOR_ID(+) = AI.VENDOR_ID /* BUG:7366363 */
AND AI.APPROVAL_READY_FLAG <> 'S'
|
|
|
|