SELECT aba.batch_name invoice_batch,
aia.source invoice_source,
aia.org_id,
hou.name OU_Name,
pca.checkrun_name,
pca.check_date,
pca.check_number document_number,
(select user_name from fnd_user where user_id = pca.created_by) checkrun_createdby,
pca.attribute5 void_rsn,
pca.attribute7 utr_no,
pca.attribute8 txn_type,
pca.bank_Account_num remit_to,
pca.bank_account_name,
pca.void_date,
aia.invoice_num,
aia.batch_id,
aia.invoice_id,
aia.doc_sequence_value voucher_num,
REPLACE(REPLACE(aia.description,CHR(10),''),CHR(13),'') description,
aia.invoice_currency_code,
aia.payment_currency_code,
aia.invoice_date,
aia.invoice_amount,
aia.amount_paid,
aia.gl_date,
aipa.payment_num,
aipa.amount payment_amount,
aipa.accounting_date payment_date,
REPLACE(REPLACE(asi.vendor_name,CHR(10),''),CHR(13),'') vendor_name,
REPLACE(REPLACE(asi.vendor_name_alt,CHR(10),''),CHR(13),'') vendor_name_alt,
asi.segment1 vendor_number,
aia.pay_group_lookup_code pay_group,
REPLACE(REPLACE(assa.vendor_site_code,CHR(10),''),CHR(13),'') vendor_site_code,
REPLACE(REPLACE(assa.vendor_site_code_alt,CHR(10),''),CHR(13),'') vendor_site_code_alt,
flv_vendortype.meaning vendor_type,
flv_invoicetype.meaning invoice_type,
flv_paytype.meaning payment_type,
flv_paystatus.meaning payment_status,
(SELECT gross_amount
FROM AP_PAYMENT_SCHEDULES_ALL
WHERE INVOICE_ID = aia.invoice_id
AND PAYMENT_NUM = aipa.payment_num
AND ROWNUM=1) schedule_amount,
(SELECT due_Date
FROM AP_PAYMENT_SCHEDULES_ALL
WHERE INVOICE_ID = aia.invoice_id
AND PAYMENT_NUM = aipa.payment_num
AND ROWNUM=1) due_date,
aia.accts_pay_code_combination_id,
DECODE(pca.void_date,NULL,'Negotiable','Void') inv_status,
pca.check_id
FROM ap_checks_all pca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asi,
ap_supplier_sites_all assa,
ap_batches_all aba,
fnd_lookup_values flv_vendortype,
fnd_lookup_values flv_invoicetype,
fnd_lookup_values flv_paytype,
fnd_lookup_values flv_paystatus
WHERE 1=1
AND flv_paystatus.lookup_code = aia.payment_status_flag
AND flv_paytype.lookup_code = pca.payment_type_flag
AND flv_invoicetype.lookup_code = aia.invoice_type_lookup_code
AND flv_vendortype.lookup_code = asi.vendor_type_lookup_code
AND aba.batch_id(+) = aia.batch_id
AND assa.org_id = aipa.org_id
AND assa.vendor_site_id = aia.vendor_site_id
AND asi.vendor_id = aia.vendor_id
AND aipa.org_id = hou.organization_id
AND hou.set_of_books_id = aipa.set_of_books_id
AND aia.invoice_id = aipa.invoice_id
and pca.org_id = aipa.org_id
AND pca.check_id = aipa.check_id
AND flv_invoicetype.language = 'US'
AND flv_vendortype.language = 'US'
AND flv_paytype.language = 'US'
AND flv_paystatus.language = 'US'
AND NVL(flv_paystatus.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_invoicetype.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_vendortype.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_paytype.end_date_active,SYSDATE+1) > SYSDATE
AND flv_invoicetype.lookup_type = 'INVOICE TYPE'
AND flv_vendortype.lookup_type = 'VENDOR TYPE'
AND flv_paytype.lookup_type = 'PAYMENT TYPE'
AND flv_paystatus.lookup_type = 'INVOICE PAYMENT STATUS'
AND aipa.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND TRUNC(NVL(aipa.accounting_date,SYSDATE)) BETWEEN NVL (TRUNC (TO_DATE (p_txndate_from,
'YYYY/MM/DD HH24:MI:SS')),
TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND NVL (p_txndate_to,
TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND asi.vendor_id = NVL(p_vendorid,asi.vendor_id)
AND aia.org_id = NVL(p_ou_id, aia.org_id)
AND asi.vendor_type_lookup_code = NVL(p_vendortype, asi.vendor_type_lookup_code)
ORDER BY invoice_num
,aipa.payment_num
,accounting_date;
aia.source invoice_source,
aia.org_id,
hou.name OU_Name,
pca.checkrun_name,
pca.check_date,
pca.check_number document_number,
(select user_name from fnd_user where user_id = pca.created_by) checkrun_createdby,
pca.attribute5 void_rsn,
pca.attribute7 utr_no,
pca.attribute8 txn_type,
pca.bank_Account_num remit_to,
pca.bank_account_name,
pca.void_date,
aia.invoice_num,
aia.batch_id,
aia.invoice_id,
aia.doc_sequence_value voucher_num,
REPLACE(REPLACE(aia.description,CHR(10),''),CHR(13),'') description,
aia.invoice_currency_code,
aia.payment_currency_code,
aia.invoice_date,
aia.invoice_amount,
aia.amount_paid,
aia.gl_date,
aipa.payment_num,
aipa.amount payment_amount,
aipa.accounting_date payment_date,
REPLACE(REPLACE(asi.vendor_name,CHR(10),''),CHR(13),'') vendor_name,
REPLACE(REPLACE(asi.vendor_name_alt,CHR(10),''),CHR(13),'') vendor_name_alt,
asi.segment1 vendor_number,
aia.pay_group_lookup_code pay_group,
REPLACE(REPLACE(assa.vendor_site_code,CHR(10),''),CHR(13),'') vendor_site_code,
REPLACE(REPLACE(assa.vendor_site_code_alt,CHR(10),''),CHR(13),'') vendor_site_code_alt,
flv_vendortype.meaning vendor_type,
flv_invoicetype.meaning invoice_type,
flv_paytype.meaning payment_type,
flv_paystatus.meaning payment_status,
(SELECT gross_amount
FROM AP_PAYMENT_SCHEDULES_ALL
WHERE INVOICE_ID = aia.invoice_id
AND PAYMENT_NUM = aipa.payment_num
AND ROWNUM=1) schedule_amount,
(SELECT due_Date
FROM AP_PAYMENT_SCHEDULES_ALL
WHERE INVOICE_ID = aia.invoice_id
AND PAYMENT_NUM = aipa.payment_num
AND ROWNUM=1) due_date,
aia.accts_pay_code_combination_id,
DECODE(pca.void_date,NULL,'Negotiable','Void') inv_status,
pca.check_id
FROM ap_checks_all pca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers asi,
ap_supplier_sites_all assa,
ap_batches_all aba,
fnd_lookup_values flv_vendortype,
fnd_lookup_values flv_invoicetype,
fnd_lookup_values flv_paytype,
fnd_lookup_values flv_paystatus
WHERE 1=1
AND flv_paystatus.lookup_code = aia.payment_status_flag
AND flv_paytype.lookup_code = pca.payment_type_flag
AND flv_invoicetype.lookup_code = aia.invoice_type_lookup_code
AND flv_vendortype.lookup_code = asi.vendor_type_lookup_code
AND aba.batch_id(+) = aia.batch_id
AND assa.org_id = aipa.org_id
AND assa.vendor_site_id = aia.vendor_site_id
AND asi.vendor_id = aia.vendor_id
AND aipa.org_id = hou.organization_id
AND hou.set_of_books_id = aipa.set_of_books_id
AND aia.invoice_id = aipa.invoice_id
and pca.org_id = aipa.org_id
AND pca.check_id = aipa.check_id
AND flv_invoicetype.language = 'US'
AND flv_vendortype.language = 'US'
AND flv_paytype.language = 'US'
AND flv_paystatus.language = 'US'
AND NVL(flv_paystatus.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_invoicetype.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_vendortype.end_date_active,SYSDATE+1) > SYSDATE
AND NVL(flv_paytype.end_date_active,SYSDATE+1) > SYSDATE
AND flv_invoicetype.lookup_type = 'INVOICE TYPE'
AND flv_vendortype.lookup_type = 'VENDOR TYPE'
AND flv_paytype.lookup_type = 'PAYMENT TYPE'
AND flv_paystatus.lookup_type = 'INVOICE PAYMENT STATUS'
AND aipa.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND TRUNC(NVL(aipa.accounting_date,SYSDATE)) BETWEEN NVL (TRUNC (TO_DATE (p_txndate_from,
'YYYY/MM/DD HH24:MI:SS')),
TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND NVL (p_txndate_to,
TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND asi.vendor_id = NVL(p_vendorid,asi.vendor_id)
AND aia.org_id = NVL(p_ou_id, aia.org_id)
AND asi.vendor_type_lookup_code = NVL(p_vendortype, asi.vendor_type_lookup_code)
ORDER BY invoice_num
,aipa.payment_num
,accounting_date;
No comments:
Post a Comment