hi this is the Sample requirement.
Solution
----------
For this i used below query.
SELECT DISTINCT
pv.vendor_name C_VENDOR,
hou.name Operating_Unit,
pv.vendor_id C_VENDOR_ID,
pvs.vendor_site_code C_VENDOR_SITE,
i.invoice_num C_INVOICE_NUMBER,
i.invoice_id C_INVOICE_ID1,
i.invoice_date C_TRANSACTION_DATE,
i.invoice_type_lookup_code C_TRANSACTION_TYPE,
alc.displayed_field C_TRANSACTION_TYPE_FIELD,
i.payment_currency_code C_CURR1,
DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
f2.name C_DOC_SEQUENCE_NAME,
DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0), nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id),
nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
ck.check_number C_TRANSACTION_NUMBER,
f.name C_DOC_SEQUENCE_NAME1,
aip.accounting_date C_TRANSACTION_DATE2,
ael.accounting_class_code C_LINE_TYPE_CODE,
DECODE(ael.accounting_class_code, 'CASH', 'Cash',
'DISCOUNT','Discount',
'GAIN','Gain',
'LOSS','Loss',
'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
ck.currency_code C_CURR2,
DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
'CASH_CLEARING',-1*aip.amount,
'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount), 'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
)C_TRANSACTION_AMT ,
invoice_payment_id C_INVOICE_PAYMENT_ID
FROM po_vendors pv,
po_vendor_sites pvs,
ap_invoices i,
ap_checks ck,
fnd_document_sequences f,
fnd_document_sequences f2,
ap_invoice_payments aip,
xla_transaction_entities ATE,
xla_events AAE ,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_lookup_codes alc ,
ap_payment_history aph,
ap_system_parameters ASP,
hr_operating_units hou
WHERE ASP.when_to_account_pmt = 'CLEARING ONLY'
AND ASP.org_id = i.org_id
AND pv.vendor_id = pvs.vendor_id
AND i.vendor_id = pv.vendor_id
AND i.vendor_site_id = pvs.vendor_site_id
AND i.invoice_id = aip.invoice_id (+)
AND i.invoice_type_lookup_code = alc.lookup_code
AND alc.lookup_type = 'INVOICE TYPE'
AND aip.check_id = aph.check_id (+)
AND aph.accounting_event_id = AEH.event_id(+)
AND NVL( AAE.event_status_code , 'X' ) <> 'N'
AND AEH.event_id = AAE.event_id(+)
AND AAE.entity_id = ATE.entity_id(+)
AND AEH.ae_header_id = AEL.ae_header_id(+)
AND ATE.entity_code(+) = 'AP_PAYMENTS'
AND aeh.ledger_id(+) = :P_BOOk
AND ck.check_id (+)= aip.check_id
AND f.doc_sequence_id (+) = ck.doc_sequence_id
AND f2.doc_sequence_id(+)= i.doc_sequence_id
AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1) <> DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
AND alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')
AND hou.organization_id=i.org_id
AND hou.set_of_books_id=i.set_of_books_id
AND i.invoice_date<:P_AS_OF_DATE
&LP_VENDOR_ID
&LP_VENDOR_SITE
&LP_INVOICES
&LP_INVOICES_NUMBER_FROM
&LP_INVOICES_NUMBER_TO
&LP_DOC_SEQUENCE_NAME
&LP_DOC_SEQUENCE_NUMBER_FROM
&LP_DOC_SEQUENCE_NUMBER_TO
-- &LP_INVOICE_DATE_FROM
-- &LP_INVOICE_DATE_TO
UNION
SELECT DISTINCT
pv.vendor_name C_VENDOR,
hou.name Operating_Unit,
pv.vendor_id C_VENDOR_ID,
pvs.vendor_site_code C_VENDOR_SITE,
i.invoice_num C_INVOICE_NUMBER,
i.invoice_id C_INVOICE_ID1,
i.invoice_date C_TRANSACTION_DATE,
i.invoice_type_lookup_code C_TRANSACTION_TYPE,
alc.displayed_field C_TRANSACTION_TYPE_FIELD,
i.payment_currency_code C_CURR,
DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
f2.name C_DOC_SEQUENCE_NAME,
DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0), nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id),
nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
ck.check_number C_TRANSACTION_NUMBER,
f.name C_DOC_SEQUENCE_NAME1,
aip.accounting_date C_TRANSACTION_DATE2,
ael.accounting_class_code C_LINE_TYPE_CODE,
DECODE(ael.accounting_class_code, 'CASH', 'Cash',
'DISCOUNT','Discount',
'GAIN','Gain',
'LOSS','Loss',
'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
ck.currency_code C_CURR2,
DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
'CASH_CLEARING',-1*aip.amount,
'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount), 'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id C_INVOICE_PAYMENT_ID
FROM po_vendors pv,
po_vendor_sites pvs,
ap_invoices i,
ap_checks ck,
fnd_document_sequences f,
fnd_document_sequences f2,
ap_invoice_payments aip,
xla_transaction_entities ATE,
xla_events AAE ,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_lookup_codes alc,
ap_system_parameters ASP,
hr_operating_units hou
WHERE ASP.when_to_account_pmt <> 'CLEARING ONLY'
AND ASP.org_id = i.org_id
AND pv.vendor_id = pvs.vendor_id
AND i.vendor_id = pv.vendor_id
AND i.vendor_site_id = pvs.vendor_site_id
AND i.invoice_id = aip.invoice_id (+)
AND i.invoice_type_lookup_code = alc.lookup_code
AND alc.lookup_type = 'INVOICE TYPE'
AND aip.accounting_event_id = AEH.event_id(+)
AND AEH.event_id = AAE.event_id(+)
AND AAE.entity_id = ATE.entity_id(+)
AND AEH.ae_header_id = AEL.ae_header_id(+)
AND ATE.entity_code(+) = 'PAYMENTS'
AND aeh.ledger_id(+) = :P_BOOk
AND ck.check_id (+)= aip.check_id
AND f.doc_sequence_id (+) = ck.doc_sequence_id
AND f2.doc_sequence_id(+)= i.doc_sequence_id
AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1) <> DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
AND alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')
AND hou.organization_id=i.org_id
AND hou.set_of_books_id=i.set_of_books_id
AND i.invoice_date<:P_AS_OF_DATE
&LP_VENDOR_ID
&LP_VENDOR_SITE
&LP_INVOICES
&LP_INVOICES_NUMBER_FROM
&LP_INVOICES_NUMBER_TO
&LP_DOC_SEQUENCE_NAME
&LP_DOC_SEQUENCE_NUMBER_FROM
&LP_DOC_SEQUENCE_NUMBER_TO
--&LP_INVOICE_DATE_FROM
--&LP_INVOICE_DATE_TO
UNION
SELECT DISTINCT
pv.vendor_name C_VENDOR,
hou.name Operating_Unit,
pv.vendor_id C_VENDOR_ID,
pvs.vendor_site_code C_VENDOR_SITE,
i.invoice_num C_INVOICE_NUMBER,
i.invoice_id C_INVOICE_ID1,
i.invoice_date C_TRANSACTION_DATE,
i.invoice_type_lookup_code C_TRANSACTION_TYPE,
alc.displayed_field C_TRANSACTION_TYPE_FIELD,
i.payment_currency_code C_CURR,
DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
f2.name C_DOC_SEQUENCE_NAME,
DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0), nvl(ap_invoices_pkg.Get_Line_Total_Incl_Prepay(i.invoice_id), nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0))) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
ck.check_number C_TRANSACTION_NUMBER,
f.name C_DOC_SEQUENCE_NAME1,
aip.accounting_date C_TRANSACTION_DATE2,
ael.accounting_class_code C_LINE_TYPE_CODE,
DECODE(ael.accounting_class_code, 'CASH', 'Cash',
'DISCOUNT','Discount',
'GAIN','Gain',
'LOSS','Loss',
'CASH_CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
ck.currency_code C_CURR2,
DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
'CASH_CLEARING',-1*aip.amount,
'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount), 'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
--Bug2909396 Rounding the DISCOUNT amount
)C_TRANSACTION_AMT ,
invoice_payment_id C_INVOICE_PAYMENT_ID
FROM po_vendors pv,
po_vendor_sites pvs,
ap_invoices i,
ap_checks ck,
fnd_document_sequences f,
fnd_document_sequences f2,
ap_invoice_payments aip,
xla_transaction_entities ATE,
xla_events AAE ,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_lookup_codes alc,
ap_payment_history aph,
hr_operating_units hou
WHERE pv.vendor_id = pvs.vendor_id
AND i.vendor_id = pv.vendor_id
AND i.vendor_site_id = pvs.vendor_site_id
AND i.invoice_id = aip.invoice_id
AND i.invoice_type_lookup_code = alc.lookup_code
AND alc.lookup_type = 'INVOICE TYPE'
AND aip.accounting_event_id = AEH.event_id
AND AEH.event_id = AAE.event_id
AND AAE.entity_id = ATE.entity_id
AND AEH.ae_header_id = AEL.ae_header_id
AND ATE.entity_code = 'PAYMENTS'
AND aeh.ledger_id = :P_BOOk
AND ck.check_id = aip.check_id
AND f.doc_sequence_id (+) = ck.doc_sequence_id
AND f2.doc_sequence_id(+)= i.doc_sequence_id
AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1)
<> DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
AND aph.accounting_event_id = aeh.event_id
AND aip.check_id = aph.check_id
AND aph.transaction_type = 'PAYMENT MATURITY'
AND alc.displayed_field not in ('PREPAYMENT','CASH','Prepayment','Cash')
AND hou.organization_id=i.org_id
AND hou.set_of_books_id=i.set_of_books_id
AND i.invoice_date<:P_AS_OF_DATE
&LP_VENDOR_ID
&LP_VENDOR_SITE
&LP_INVOICES
&LP_INVOICES_NUMBER_FROM
&LP_INVOICES_NUMBER_TO
&LP_DOC_SEQUENCE_NAME
&LP_DOC_SEQUENCE_NUMBER_FROM
&LP_DOC_SEQUENCE_NUMBER_TO
--&LP_INVOICE_DATE_FROM
-- &LP_INVOICE_DATE_TO
ORDER BY C_VENDOR,
C_VENDOR_SITE,
C_INVOICE_NUMBER,
C_TRANSACTION_DATE,
C_TRANSACTION_TYPE,
C_TRANSACTION_DATE2,
C_LINE_TYPE_CODE,
C_INVOICE_PAYMENT_ID
rdf look like this
And the sample layout shown below
Invoice History Report
|
Operating Unit
|
:
|
OPUNIT
|
|
As of Date
|
:
|
DATE
|
|
Operating Unit
|
|
|||||||||||||||||||||||||
|
Here i took 3 groups operating unit,vendor name and invoice number.
Hope this will help you.
