https://aporaclepayables.blogspot.com/2017/11/app-sqlap-10000-ora-01403-no-data-found.html
ERROR
Unable to validate invoice where the item line has a withholding tax group due to:
APP-SQLAP-10000: ORA-01403: no data found occurred in with parameters (Term id = Sequence num = 1) while performing the following operation: &DEBUG_INFO the following error occurs.
SOLUTION
Step 1. Navigate to Supplier form through Payables > Suppliers > Entry
Step 2. Query the Supplier linked to the withholding group.
Step 3. Click Update for updating the Supplier.
Step 4. Select the Key Payment Setup tab.
Step 5. Scroll to the Payment terms section and add a Payment Term to the Tax Authority for that Vendor Site.
Step 6. Retest the issue.
Thursday 23 November 2017
Java Heap Space Error while running Payables Posted Invoice Register oracle.solutions July 1, 2017 Accounts Payables, Errors/Workarounds-AP, Oracle Functional Leave a comment 57 Views
https://aporaclepayables.blogspot.com/2017/11/java-heap-space-error-while-running.html
ERROR
When attempting to run Payables Posted Invoice Register ,
the following error occurs.
-----------------------------
Status=Error
Completion Text=Java Heap Space
SOLUTION
Step 1. Set the Java Virtual Memory (JVM) for the report to a higher value.
Responsibility: Application Developer or System Administrator
Navigate: Concurrent > Program
Query the Concurrent Program – query Report which is experiencing the issue
In the ‘Executable’ block, there is a field called ‘Options’. (It should be empty initially for the Concurrent Program).
Set this field to increase the maximum amount of memory – use the appropriate value based on the amount of datawhich is expected to be processed.
-Xmx512m
-Xmx1024M
-Xmx2048M
Save the concurrent program definition.
Step 2. Retest the Issue
Step 3. Migrate the solution as appropriate to other environments.
Reference : Java Heap Space Error Running Payables Posted Invoice Register (Doc ID 1616683.1)
ERROR
When attempting to run Payables Posted Invoice Register ,
the following error occurs.
-----------------------------
Status=Error
Completion Text=Java Heap Space
SOLUTION
Step 1. Set the Java Virtual Memory (JVM) for the report to a higher value.
Responsibility: Application Developer or System Administrator
Navigate: Concurrent > Program
Query the Concurrent Program – query Report which is experiencing the issue
In the ‘Executable’ block, there is a field called ‘Options’. (It should be empty initially for the Concurrent Program).
Set this field to increase the maximum amount of memory – use the appropriate value based on the amount of datawhich is expected to be processed.
-Xmx512m
-Xmx1024M
-Xmx2048M
Save the concurrent program definition.
Step 2. Retest the Issue
Step 3. Migrate the solution as appropriate to other environments.
Reference : Java Heap Space Error Running Payables Posted Invoice Register (Doc ID 1616683.1)
Monday 13 November 2017
An internal error has occurred in the program xla_ae_lines_pkg.AccountingReversal. ORA-01555: snapshot too old: rollback segment number 7 with name "$TEMPUNDOSEG" too small.
https://aporaclepayables.blogspot.com/2017/11/error-internal-error-has-occurred-in.html
Error
Error
An internal error has occurred in the program xla_ae_lines_pkg.AccountingReversal.
ORA-01555: snapshot too old: rollback segment number 7 with name "$TEMPUNDOSEG" too
small.
An internal error occurred. Please inform your system administrator or support
representative that:
An internal error has occurred in the program xla_ae_lines_pkg.AccountingReversal.
ORA-01555: snapshot too old: rollback segment number 7 with name "$TE
For my Case
1: Data file 7 gb to 11gb Increased
alter tablespace APPS_UNDOTS1 add datafile '/data/CLIENT/PROD/apps_st/data/undo04.dbf' size 4096m
2: undo retention 900 sec to 1800 sec increased
alter system set undo retention = 1800 scope=both
Thursday 2 November 2017
Supplier Ledger
https://aporaclepayables.blogspot.com/2017/11/supplier-ledger.html
-- in case of APN Num using gl_date as base date in parameter as there is no clearing date present for APN Num
-- in case of APV Num using clearing_date as base date in parameter
SELECT 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num
, TRIM(UPPER(supp.vendor_name))||' Supp ID: '||supp.segment1 supplier_name
, 'APN' "Type"
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description "Description"
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate
, NULL debit_entered_amount
, NULL debit_func_amount
, aia.invoice_amount credit_entered_amount
, (aia.invoice_amount * nvl (aia.exchange_rate, 1)) credit_func_amount
, NULL invoice_id
, aia.vendor_id
, aia.org_id
FROM ap_invoices_all aia
, ap_suppliers supp
, gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl (:p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
AND aia.gl_date BETWEEN nvl (:p_from_date, aia.gl_date) AND nvl (:p_to_date, aia.gl_date)
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num
, TRIM(UPPER(supp.vendor_name))||' Supp ID: '||supp.segment1 supplier_name
, 'APV' "Type"
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description "Description"
, aca.currency_code ccy
, aca.exchange_rate exch_rate
, aia.invoice_amount debit_entered_amount
, ((aia.invoice_amount * nvl (aia.exchange_rate, 1))) debit_func_amount
, NULL credit_entered_amount
, NULL credit_func_amount
, aia.invoice_id
, aia.vendor_id
, aia.org_id
FROM ap_invoices_all aia
, ap_invoice_payments_all aipa
, ap_checks_all aca
, ap_suppliers supp
, gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl (:p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
AND aca.cleared_date BETWEEN nvl (:p_from_date, aca.cleared_date) AND nvl (:p_to_date, aca.cleared_date)
------------------------------------------------------------------
ORDER BY supplier_name, gl_date, "Type", doc_value
-- in case of APN Num using gl_date as base date in parameter as there is no clearing date present for APN Num
-- in case of APV Num using clearing_date as base date in parameter
SELECT 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num
, TRIM(UPPER(supp.vendor_name))||' Supp ID: '||supp.segment1 supplier_name
, 'APN' "Type"
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description "Description"
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate
, NULL debit_entered_amount
, NULL debit_func_amount
, aia.invoice_amount credit_entered_amount
, (aia.invoice_amount * nvl (aia.exchange_rate, 1)) credit_func_amount
, NULL invoice_id
, aia.vendor_id
, aia.org_id
FROM ap_invoices_all aia
, ap_suppliers supp
, gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl (:p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
AND aia.gl_date BETWEEN nvl (:p_from_date, aia.gl_date) AND nvl (:p_to_date, aia.gl_date)
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num
, TRIM(UPPER(supp.vendor_name))||' Supp ID: '||supp.segment1 supplier_name
, 'APV' "Type"
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description "Description"
, aca.currency_code ccy
, aca.exchange_rate exch_rate
, aia.invoice_amount debit_entered_amount
, ((aia.invoice_amount * nvl (aia.exchange_rate, 1))) debit_func_amount
, NULL credit_entered_amount
, NULL credit_func_amount
, aia.invoice_id
, aia.vendor_id
, aia.org_id
FROM ap_invoices_all aia
, ap_invoice_payments_all aipa
, ap_checks_all aca
, ap_suppliers supp
, gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl (:p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, supp.vendor_name) AND nvl(:cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN nvl (:p_from_curr, aia.invoice_currency_code) AND nvl (:p_to_curr, aia.invoice_currency_code)
AND aca.cleared_date BETWEEN nvl (:p_from_date, aca.cleared_date) AND nvl (:p_to_date, aca.cleared_date)
------------------------------------------------------------------
ORDER BY supplier_name, gl_date, "Type", doc_value
PLSQL for help in Supplier Ledger
https://aporaclepayables.blogspot.com/2017/11/plsql-for-help-in-supplier-ledger.html
Opening
select VENDOR_NAME,
sum(NVL(debit_entered_amount_opn, 0)) -
sum(NVL(credit_entered_amount_opn, 0)) OPN_ENTERED,
sum(NVL(debit_func_amount_opn, 0)) -
sum(NVL(credit_func_amount_opn, 0)) OPN_ACCOUNTED
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount_opn,
NULL debit_func_amount_opn,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount_opn
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) < &p_from_date
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(nvl(aia.invoice_amount, 0)) debit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount_opn,
NULL credit_entered_amount_opn,
NULL credit_func_amount_opn
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) < &p_from_date
------------------------------------------------------------------
group by supp.VENDOR_NAME)
group by VENDOR_NAME
--------------------------------------------------------------------------------------------------------------------------
Period Activity
select a.VENDOR_NAME,
------------------
/*sum(NVL(a.debit_entered_amount,0)) DR_entered, sum(NVL(a.debit_func_amount,0)) DR_Func
,sum(NVL(a.credit_entered_amount,0)) CR_ENTERED, sum(NVL(a.credit_func_amount,0)) CR_Func,*/
sum(NVL(a.debit_entered_amount, 0)) -
sum(NVL(a.credit_entered_amount, 0)) NET_ENTERED,
sum(NVL(a.debit_func_amount, 0)) - sum(NVL(a.credit_func_amount, 0)) NET_FUNC
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount,
NULL debit_func_amount,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) BETWEEN nvl(&p_from_date, aia.gl_date) AND
nvl(&p_to_date, aia.gl_date)
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(NVL(aia.invoice_amount, 0)) debit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount,
NULL credit_entered_amount,
NULL credit_func_amount
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) BETWEEN
nvl(&p_from_date, aca.cleared_date) AND
nvl(&p_to_date, aca.cleared_date)
------------------------------------------------------------------
group by supp.VENDOR_NAME) a
--ORDER BY VENDOR_NAME/*, gl_date, Type, doc_value*/
group by VENDOR_NAME
Opening
select VENDOR_NAME,
sum(NVL(debit_entered_amount_opn, 0)) -
sum(NVL(credit_entered_amount_opn, 0)) OPN_ENTERED,
sum(NVL(debit_func_amount_opn, 0)) -
sum(NVL(credit_func_amount_opn, 0)) OPN_ACCOUNTED
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount_opn,
NULL debit_func_amount_opn,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount_opn
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) < &p_from_date
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(nvl(aia.invoice_amount, 0)) debit_entered_amount_opn,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount_opn,
NULL credit_entered_amount_opn,
NULL credit_func_amount_opn
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) < &p_from_date
------------------------------------------------------------------
group by supp.VENDOR_NAME)
group by VENDOR_NAME
--------------------------------------------------------------------------------------------------------------------------
Period Activity
select a.VENDOR_NAME,
------------------
/*sum(NVL(a.debit_entered_amount,0)) DR_entered, sum(NVL(a.debit_func_amount,0)) DR_Func
,sum(NVL(a.credit_entered_amount,0)) CR_ENTERED, sum(NVL(a.credit_func_amount,0)) CR_Func,*/
sum(NVL(a.debit_entered_amount, 0)) -
sum(NVL(a.credit_entered_amount, 0)) NET_ENTERED,
sum(NVL(a.debit_func_amount, 0)) - sum(NVL(a.credit_func_amount, 0)) NET_FUNC
from (
SELECT /* 1 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, = 'APN' Type
, aia.invoice_num inv_number
, aia.gl_date gl_date
, aia.doc_sequence_value doc_value
, -- NULL check_number
(SELECT
to_char(acax.doc_sequence_value) doc_value
FROM
ap_invoice_payments_all aipax
, ap_checks_all acax
WHERE aia.invoice_id = aipax.invoice_id
AND aipax.reversal_flag = 'N'
AND aipax.check_id = acax.check_id
AND acax.cleared_date IS NOT NULL
) check_number
, aia.description Description
, aia.invoice_currency_code ccy
, aia.exchange_rate exch_rate*/,
NULL debit_entered_amount,
NULL debit_func_amount,
sum(NVL(aia.invoice_amount, 0)) credit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) credit_func_amount
/*, NULL invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.vendor_id = supp.vendor_id
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ----------------------------------------------------------------------------------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aia.gl_date) BETWEEN nvl(&p_from_date, aia.gl_date) AND
nvl(&p_to_date, aia.gl_date)
group by supp.VENDOR_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT /* 2 sno
, gcc.segment1 company
, supp.segment1 supplier_num*/
supp.VENDOR_NAME
/*, 'APV' Type
, NULL inv_number
, aca.cleared_date gl_date
, aca.doc_sequence_value doc_value
, to_char (aca.check_number) check_number
, aca.description Description
, aca.currency_code ccy
, aca.exchange_rate exch_rate*/,
sum(NVL(aia.invoice_amount, 0)) debit_entered_amount,
sum(NVL(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount,
NULL credit_entered_amount,
NULL credit_func_amount
/*, aia.invoice_id
, aia.vendor_id
, aia.org_id*/
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aia.vendor_id = supp.vendor_id
AND aipa.accts_pay_code_combination_id = gcc.code_combination_id
AND aipa.reversal_flag = 'N'
AND aca.cleared_date IS NOT NULL
-- AND ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) = 'APPROVED'
---------- Parameters ---------------------------------------------
AND aia.org_id = nvl(&p_org_id, aia.org_id)
-- AND gcc.segment1 BETWEEN nvl (:p_from_comp, gcc.segment1) AND nvl (:p_to_comp, gcc.segment1) -- no need of company segment, b/c org_id or ledger_id automatically be taken by profile options
AND supp.vendor_name BETWEEN
nvl(&cf_from_vendor_dsp, supp.vendor_name) AND
nvl(&cf_to_vendor_dsp, supp.vendor_name)
AND aia.invoice_currency_code BETWEEN
nvl(&p_from_curr, aia.invoice_currency_code) AND
nvl(&p_to_curr, aia.invoice_currency_code)
AND trunc(aca.cleared_date) BETWEEN
nvl(&p_from_date, aca.cleared_date) AND
nvl(&p_to_date, aca.cleared_date)
------------------------------------------------------------------
group by supp.VENDOR_NAME) a
--ORDER BY VENDOR_NAME/*, gl_date, Type, doc_value*/
group by VENDOR_NAME
--------------------------------------------------------------------------------------------------------------------------
Cancellations
SELECT --qry,
-- segment1,
vendor_name,
-- invoice_currency_code,
/* SUM (NVL(credit_entered_amount, 0)) - SUM (NVL(debit_entered_amount, 0)) \*+ sum(NVL(can_amount_a,0))*\ opening_bal_entered,
sum(can_amount_a) can_amount_a,
sum(can_amount_b) can_amount_b,*/
sum(can_amount_a) - sum(can_amount_b) ENT_CAN_ENT,
sum(nvl(can_amount_a, 0) * nvl(exchange_rate, 1)) -
sum(nvl(can_amount_b, 0) * nvl(exchange_rate, 1)) NET_CAN_ACCT
/*, SUM (NVL(credit_func_amount,0)) - SUM(NVL(debit_func_amount,0)) opening_bal_func*/
-- segment2 loc -- Column added for Location --Marked on 18-JAN-2017
-- invoice_id, invoice_num,invoice_date, doc_sequence_value, terms_date -- Columns added for breakup report --Marked on 18-JAN-2017
FROM ( -- INLINE VIEW STARTS HEARE
SELECT /*1 qry ,*/
sum(nvl(can.cancelled_amount, 0)) debit_entered_amount,
sum(nvl(can.cancelled_amount, 0) * nvl(aia.exchange_rate, 1)) debit_func_amount,
sum(aia.invoice_amount + nvl(aia.cancelled_amount, 0)) credit_entered_amount,
sum((nvl(aia.invoice_amount, 0) + nvl(aia.cancelled_amount, 0)) *
nvl(aia.exchange_rate, 1)) credit_func_amount,
supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
gcc.segment2, -- Column added for Location
aia.invoice_id, -- Column added for breakup report
aia.invoice_num, -- Column added for breakup report
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date, -- Columns added for breakup report
sum(nvl(can.cancelled_amount, 0)) can_amount_a,
sum(nvl(aia.cancelled_amount, 0)) can_amount_b
FROM ap_invoices_all aia,
(select a.invoice_id,
avg(a.INVOICE_AMOUNT + a.CANCELLED_AMOUNT) cancelled_amount
from ap_invoices_all a, ap_invoice_distributions_all b
where 1 = 1
and a.invoice_id = b.invoice_id
-- and cancelled_date is not null
and trunc(b.accounting_date) <= &p_to_date
-- and b.REVERSAL_FLAG = 'Y'
-- and b.AMOUNT < 0 and
and B.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and a.org_id = &P_ORG_ID -- ADDED ON 31-JUL-15
AND B.ORG_ID = &P_ORG_ID -- ADDED ON 31-JUL-15
AND A.ORG_ID = B.ORG_ID -- ADDED ON 31-JUL-15
group by a.invoice_id
having nvl(sum(b.AMOUNT), 0) = 0
UNION
select distinct a.invoice_id, a.cancelled_amount
from ap_invoices_all a
where not exists (select 1
from ap_invoice_distributions_all b
where b.INVOICE_ID = a.INVOICE_ID)
and a.CANCELLED_DATE is not null
and a.CANCELLED_DATE <= &p_to_date
) can,
ap_suppliers supp,
gl_code_combinations gcc
WHERE 1 = 1
and
-- aia.INVOICE_ID = 94152 and
-- aia.INVOICE_NUM = '18MAY4' and
-- aia.INVOICE_NUM = 'JV-14-08' and
aia.vendor_id = supp.vendor_id
and AIA.invoice_id = can.invoice_id(+)
and aia.accts_pay_code_combination_id = gcc.code_combination_id
/* and
ap_invoices_pkg.get_approval_status (aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) in ('APPROVED','AVAILABLE','CANCELLED') */
and supp.segment1 between nvl(&vendor_id, supp.segment1) and
nvl(&to_vendor_id, supp.segment1)
/* and
aia.invoice_currency_code between &ccy and &to_ccy \*'EUR' and 'USD' *\*/
and aia.gl_date <= &p_to_date
AND aia.org_id = &P_ORG_ID -- ADDED ON 31-JUL-15
group by supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
aia.invoice_id,
aia.invoice_num,
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date,
gcc.segment2
UNION
SELECT /*aipa.* */ /*2 , */
sum(
(select nvl(aia.invoice_amount, 0) + nvl(aia.CANCELLED_AMOUNT, 0)
from ap_payment_history_all h2
where h2.PAYMENT_HISTORY_ID in
(select max(h.PAYMENT_HISTORY_ID)
from ap_payment_history_all h
where 1 = 1
and trunc(h.ACCOUNTING_DATE) <= &p_to_date
and h.TRANSACTION_TYPE not in
('PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED')
and h.CHECK_ID = aca.CHECK_ID)
and h2.TRANSACTION_TYPE = 'PAYMENT CLEARING')) debit_entered_amount,
sum(((((select nvl(aia.invoice_amount, 0) +
nvl(aia.CANCELLED_AMOUNT, 0)
from ap_payment_history_all h2
where h2.PAYMENT_HISTORY_ID in
(select max(h.PAYMENT_HISTORY_ID)
from ap_payment_history_all h
where 1 = 1
and trunc(h.ACCOUNTING_DATE) <= &p_to_date
and h.TRANSACTION_TYPE not in
('PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED')
and h.CHECK_ID = aca.CHECK_ID)
and h2.TRANSACTION_TYPE = 'PAYMENT CLEARING')) *
nvl(aia.exchange_rate, 1)))) debit_func_amount,
NULL credit_entered_amount,
NULL credit_func_amount,
supp.segment1,
supp.vendor_name,
aia.invoice_currency_code, -- Column added for breakup report
gcc.segment2, -- Column added for Location
aia.invoice_id, -- Column added for breakup report
aia.invoice_num, -- Column added for breakup report
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date, -- Columns added for breakup report
null can_amount_a,
null can_amount_b
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_suppliers supp,
gl_code_combinations gcc
WHERE 1 = 1
and
--aia.INVOICE_ID = 94152 and
--aia.INVOICE_NUM = '18MAY4' and
-- aia.INVOICE_NUM = 'JV-14-08' and
aia.invoice_id = aipa.invoice_id
and aipa.check_id = aca.check_id
and aia.vendor_id = supp.vendor_id
and aipa.accts_pay_code_combination_id = gcc.code_combination_id
/* (
( aipa.reversal_flag = 'N' and
aca.cleared_date IS NOT NULL and
aca.cleared_date <= trunc( &p_from_date )
)
OR
(
ACA.VOID_DATE IS NOT NULL AND
AIPA.ACCOUNTING_DATE <= trunc( &p_from_date ) AND
AIPA.amount > 0
)
) AND */
and aipa.INVOICE_PAYMENT_ID in
(select min(aipa.INVOICE_PAYMENT_ID)
from ap_invoice_payments_all aipa
where aipa.CHECK_ID = aca.CHECK_ID
and aipa.INVOICE_ID = aia.invoice_id)
and supp.segment1 between nvl(&vendor_id, supp.segment1) and
nvl(&to_vendor_id, supp.segment1)
/* and
aia.invoice_currency_code between &ccy and &to_ccy \* 'EUR' and 'USD'*\ -- &ccy and &to_ccy */
and aia.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aipa.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aca.org_id = &P_ORG_ID
and -- ADDED ON 31-JUL-15
aia.org_id = aipa.org_id
and -- ADDED ON 31-JUL-15
aia.org_id = aca.org_id -- ADDED ON 31-JUL-15
group by supp.segment1,
supp.vendor_name,
aia.invoice_currency_code,
aia.invoice_id,
aia.invoice_num,
aia.EXCHANGE_RATE,
aia.invoice_date,
aia.doc_sequence_value,
aia.terms_date,
gcc.segment2
) -- INLINE VIEW ENDS HEARE
--where segment2 = NVL(&P_LOC,segment2)
group by /*qry ,*/ /*segment1,*/ --segment2, --Marked on 18-JAN-2017
vendor_name /*,invoice_currency_code*/ --,invoice_id, invoice_num, invoice_date, doc_sequence_value, terms_date ----Marked on 18-JAN-2017
having(SUM(NVL(credit_entered_amount, 0)) - SUM(NVL(debit_entered_amount, 0)) != 0 OR SUM(NVL(credit_func_amount, 0)) - SUM(NVL(debit_func_amount, 0)) != 0)
order by vendor_name
Subscribe to:
Posts (Atom)