Oracle Payables - Oracle EBS R12: 2017

Thursday, 23 November 2017

APP-SQLAP-10000: ORA-01403: no data found occurred in with parameters (Term id = Sequence num = 1)

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.

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)

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

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

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

--------------------------------------------------------------------------------------------------------------------------

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 )

)

(

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

Wednesday, 4 October 2017

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.

https://aporaclepayables.blogspot.com/2017/10/app-sqlap-10000-ora-01403-no-data-found.html

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.

Error In Payables Open Interface Import : PA Flexbuild Failed

https://aporaclepayables.blogspot.com/2017/10/error-in-payables-open-interface-import.html

ERROR

(v_check_line_project_info 4) Call pa_flexbuild
(PA Flexbuild 1) Check for PA installation and Project Info
(PA Flexbuild 1) Get User Id

(PA Flexbuild 1) PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION
(PA Flexbuild 1) PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION Failed :Insert Rejecti
on
PA_PROJECT_NOT_VALID
(Insert Rejections 1) Insert into AP_INTERFACE_REJECTIONS, REJECT CODE:PA FLEXBU
ILD FAILED

SOLUTION

To implement the solution, please perform the below steps in a TEST instance:

Step 1. Validate the record in the interface table before running the import program

Step 2. Remove any records in the interface that are related to end dated/invalid Projects

Step 3. Re-run Payables Open Interface Import

Step 4. If the issue is resolved, migrate the solution to the required instance

Wednesday, 19 July 2017

How to run any concurrent request through Tools

https://aporaclepayables.blogspot.com/2017/07/how-to-run-concurrent-request-through.html

CREATE OR REPLACE PACKAGE XX_CONC_REQ_SUBMIT_PKG
IS
v_request_id fnd_concurrent_requests.request_id%type;
PROCEDURE XX_SUBMIT_INV_VOUCHER
(
p_org_id IN NUMBER,
--p_resp_name IN VARCHAR2,
p_inv_no IN VARCHAR2,
--p_invoice_date IN DATE,
p_doc_seq_value NUMBER,
p_created_by IN NUMBER
)
;
PROCEDURE XX_SUBMIT_PAYMENT_VOUCHER
(
p_org_id IN number,
p_created_by IN NUMBER,
p_vouch_no_v IN number,
p_check_no IN number

)
;
FUNCTION XX_RET_REQUEST_ID
RETURN NUMBER;

END XX_CONC_REQ_SUBMIT_PKG;

--------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY XX_CONC_REQ_SUBMIT_PKG

----*****************-- Procedure for calling Invoice Voucher

PROCEDURE XX_SUBMIT_INV_VOUCHER

(

p_org_id IN NUMBER,

p_inv_no IN VARCHAR2,

p_doc_seq_value IN NUMBER,

p_created_by IN NUMBER

)

x_user_id NUMBER;

x_appl_id NUMBER;

x_resp_id NUMBER;

X_LEDGER_ID NUMBER;

x_pos varchar2(50):='XXX';

V_LAYOUT BOOLEAN;

v_print_option boolean;

V_RDF_FILE VARCHAR2(200); -- ADDED ON 11-DEC-15

BEGIN

x_pos:='BEFORE USER';

SELECT user_id

INTO x_user_id

FROM FND_USER

WHERE user_id = p_created_by ;

x_pos:='AFTER USER';

select OU.set_of_books_id

into X_LEDGER_ID

from hr_operating_units OU

where OU.organization_id = p_org_id ;

SELECT application_id

INTO x_appl_id

FROM FND_APPLICATION

WHERE application_short_name = 'SQLAP';

x_pos:='AFTER APPLICATION';

FND_GLOBAL.APPS_INITIALIZE(x_user_id, x_resp_id, x_appl_id);

x_pos:= 'BEFORE API';

V_LAYOUT:= FND_SUBMIT.add_layout(template_appl_name => 'SQLAP'

,template_code => 'COAPN2'

,template_language => 'en'

,template_territory => 'US'

,output_format => 'PDF');

--commit;

IF V_LAYOUT = TRUE THEN

IF p_org_id=8 THEN

V_RDF_FILE:='APAPNB';

ELSIF p_org_id=85 THEN

V_RDF_FILE:='APAPNB';

ELSIF p_org_id=36 THEN

V_RDF_FILE:='APAPNB';

ELSIF p_org_id=29 THEN

V_RDF_FILE:='APAPNB';

ELSIF p_org_id=47 THEN

V_RDF_FILE:='APAPNB';

END IF;

v_print_option := fnd_request.set_print_options(printer => 'cheque'

--,style => 'A4'

,copies => 1

,save_output => TRUE

,print_together => 'N');

v_request_id := fnd_request.submit_request(application => 'SQLAP'

, program => V_RDF_FILE --'APAPNB'

, description => NULL

,start_time => SYSDATE

,sub_request => FALSE

, argument1 => p_org_id

, argument2 => NULL--p_invoice_date

, argument3 => NULL--p_invoice_date

, argument4 => p_doc_seq_value

, argument5 => p_doc_seq_value

, argument6 => NULL --p_inv_no

, argument7 => NULL --p_inv_no

, argument8 => NULL

, argument9 => NULL --

, argument10 => NULL --

, argument11 => NULL

, argument12 => X_LEDGER_ID --Ledger ID

);

--FND_REQUEST.add_layout(template_appl_name => 'SQLAP');

END IF;

x_pos:='AFTER API';

commit;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(x_pos);

dbms_output.put_line(SQLERRM);

END;

---*****************-- Procedure for calling Payment Register

PROCEDURE XX_SUBMIT_PAYMENT_VOUCHER(

p_org_id IN number,

p_created_by IN number,

p_vouch_no_v IN number,

p_check_no IN number

)

x_user_id NUMBER;

x_appl_id NUMBER;

x_resp_id NUMBER;

x_pos varchar2(50):='XXX';

--req_id NUMBER;

v_print_option boolean;

V_RDF_FILE VARCHAR2(200); -- ADDED ON 11-DEC-15

BEGIN

x_pos:='BEFORE USER';

SELECT user_id

INTO x_user_id

FROM FND_USER

WHERE user_id = p_created_by;

x_pos:='AFTER USER';

SELECT application_id

INTO x_appl_id

FROM FND_APPLICATION

WHERE application_short_name = 'SQLAP';

x_pos:='AFTER APPLICATION';

SELECT responsibility_id

INTO x_resp_id

FROM FND_APPLICATION fa, FND_RESPONSIBILITY_TL fr

WHERE fa.application_short_name = 'SQLAP' AND

fa.application_id = fr.application_id AND

fr.responsibility_name = 'AP Super User';

x_pos:='AFTER RESPONSIBILITY';

FND_GLOBAL.APPS_INITIALIZE(x_user_id, x_resp_id, x_appl_id);

x_pos:= 'BEFORE API';

IF p_org_id=82 THEN

V_RDF_FILE:='APAPV';

ELSIF p_org_id=185 THEN

V_RDF_FILE:='APAPV';

END IF;

v_print_option := fnd_request.set_print_options(printer => 'cheque'

,style => 'A4'

,copies => 1

,save_output => TRUE

,print_together => 'N');

v_request_id := fnd_request.submit_request(application => 'SQLAP'

, program => V_RDF_FILE -- 'PAPV'

, description => NULL

,start_time => NULL

,sub_request => FALSE

, argument1 => null

, argument2 => NULL

, argument3 => NULL

, argument4 => NULL

, argument5 => p_vouch_no_v

, argument6 => p_vouch_no_v

, argument7 => p_check_no

, argument8 => p_check_no

);

x_pos:='AFTER API';

commit;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

FUNCTION XX_RET_REQUEST_ID

RETURN NUMBER AS

BEGIN

RETURN v_request_id;

END XX_RET_REQUEST_ID;

END XX_CONC_REQ_SUBMIT_PKG;

----------------------------------------------------------------------------------------------------------------------

='
begin
XX_CONC_REQ_SUBMIT_PKG.XX_SUBMIT_INV_VOUCHER('''||:INV_SUM_FOLDER.ORG_ID||''', '''||:INV_SUM_FOLDER.INVOICE_NUM||''','''||:INV_SUM_FOLDER.DOC_SEQUENCE_VALUE||''','''||:PARAMETER.USER_ID||'''
);
end'

Wednesday, 5 July 2017

How to Disable Delete Button on AP Invoice Headers

https://aporaclepayables.blogspot.com/2017/07/how-to-disable-delete-button-on-ap.html

How to Disable Delete Button on AP Invoice Headers

-----------------------------------

WHEN-NEW-RECORD-INSTANCE
INV_SUM_FOLDER

Type: Property
Block
INV_SUM_FOLDER
DELETE_ALLOWED
FALSE

Personalization to Popup Error Message on Exchange Rate field on Foreign Invoices if null

https://aporaclepayables.blogspot.com/2017/07/personalization-to-popup-error-message.html

Personalization to Popup Error Message on Exchange Rate field on Foreign Invoices if null

WHEN-VALIDATE-RECORD
INV_SUM_FOLDER

:INV_SUM_FOLDER.INVOICE_CURRENCY_CODE <> 'PKR'
and :INV_SUM_FOLDER.RATE_DSP is null

Message:

Error: Please Enter Exchange Rate Information

How to change the Cancelled Date of Invoice in AP. If Invoice has got no distributions and does not require any Accounting

https://aporaclepayables.blogspot.com/2017/07/how-to-change-cancelled-date-of-invoice.html

User has cancelled the Invoice on 01-JUL-2017. We required to change the Cancelled Date to 30-JUN-2017.

Please Note that Invoice has got no distributions and does not require any Accounting.

---------------------------------------

You can use following script to update the canceled_date. If Invoice has got no distributions and does not require any Accounting.

update ap_invoices_all
set cancelled_date = ''
where invoice_id = <invoice id >;

Tuesday, 20 June 2017

AP Invoice Distributions PLSQL QUERY (Item Line with WHT Deduction)

http://aporaclepayables.blogspot.com/2017/06/select-g.html

select g.DOC_SEQUENCE_VALUE APN_No,
g.GL_DATE HEADER_GL_DATE,
d.ACCOUNTING_DATE Distribution_GL_DATE,
b.VENDOR_NAME,
d.DESCRIPTION,
d.line_type_lookup_code,
-- d.invoice_distribution_id,
-- d.awt_related_id,
NVL(d.base_amount, d.amount) Amount,
-- d.awt_group_id,
-- d.awt_tax_rate_id,
-- L.PAY_AWT_GROUP_ID,
-- L.AWT_GROUP_ID,
(select SUM(NVL(d2.base_amount, d2.amount))
from ap_invoice_distributions_all d2
where d2.awt_related_id = d.invoice_distribution_id
and d2.line_type_lookup_code = 'AWT'
and d2.invoice_id = d.invoice_id) WHT,
/* (select tr.tax_rate
from ap_awt_tax_rates_all tr
where tr.tax_rate_id = d.awt_tax_rate_id) TAX_RATE,*/
/* (select tr.Tax_Name
from ap_awt_tax_rates_all tr
where tr.tax_rate_id = d.awt_tax_rate_id) TAX_name,*/
(select GRP.NAME
from AP_AWT_GROUPS GRP
where GRP.GROUP_ID = D.AWT_ORIGIN_GROUP_ID) AWT_GROUP,

(select grp2.name
from AP_AWT_GROUPS GRP2
where grp2.group_id = d.pay_awt_group_id) ITEM_GROUP

/* , (select nvl(d2.BASE_AMOUNT, d2.amount)
from ap_invoice_distributions_all d2
where d2.invoice_distribution_id = d.awt_related_id
and d2.line_type_lookup_code = 'ITEM'
and d2.invoice_id = d.invoice_id) ACTUAL_AMOUNT*/
from ap_invoice_distributions_All d,
AP_INVOICE_LINES_All L,
ap_invoices_all g,
ap_suppliers b
WHERE D.INVOICE_ID = L.INVOICE_ID
AND D.INVOICE_LINE_NUMBER = L.LINE_NUMBER
and g.INVOICE_ID = L.INVOICE_ID
and g.INVOICE_ID = d.INVOICE_ID
and g.ORG_ID = L.org_id
and L.org_id = d.Org_id
and b.VENDOR_ID = g.VENDOR_ID
and g.org_id = 376
--and d.invoice_id = 94273 -- 3511993--3547454

/* select sum(NVL(base_amount,amount)) from ap_invoice_distributions_all --4308
where org_id=376
and line_type_lookup_code = 'AWT'
*/

--------------------------------------------------------------------------------------------------------------------------

select --distinct g.invoice_id, g.AMOUNT_PAID --4473 --706556268 8834072
g.AMOUNT_PAID,
g.DOC_SEQUENCE_VALUE APN_No,
g.GL_DATE HEADER_GL_DATE,
d.ACCOUNTING_DATE Distribution_GL_DATE,
b.VENDOR_NAME,
d.DESCRIPTION,
d.line_type_lookup_code,
gc.SEGMENT1||'-'||gc.SEGMENT2||'-'||gc.SEGMENT3||'-'||gc.SEGMENT4||'-'||gc.SEGMENT5||'-'||gc.SEGMENT6||'-'||gc.SEGMENT7||'-'||gc.SEGMENT8 Account,
-- d.invoice_distribution_id,
-- d.awt_related_id,
NVL(d.base_amount, d.amount) Amount,
-- d.awt_group_id,
-- d.awt_tax_rate_id,
-- L.PAY_AWT_GROUP_ID,
-- L.AWT_GROUP_ID,
(select SUM(NVL(d2.base_amount, d2.amount))
from ap_invoice_distributions_all d2
where d2.awt_related_id = d.invoice_distribution_id
and d2.line_type_lookup_code = 'AWT'
and d2.invoice_id = d.invoice_id) WHT,
/* (select tr.tax_rate
from ap_awt_tax_rates_all tr
where tr.tax_rate_id = d.awt_tax_rate_id) TAX_RATE,*/
/* (select tr.Tax_Name
from ap_awt_tax_rates_all tr
where tr.tax_rate_id = d.awt_tax_rate_id) TAX_name,*/
(select GRP.NAME
from AP_AWT_GROUPS GRP
where GRP.GROUP_ID = D.AWT_ORIGIN_GROUP_ID) AWT_GROUP,

(select grp2.name
from AP_AWT_GROUPS GRP2
where grp2.group_id = d.pay_awt_group_id) ITEM_GROUP

/* , (select nvl(d2.BASE_AMOUNT, d2.amount)
from ap_invoice_distributions_all d2
where d2.invoice_distribution_id = d.awt_related_id
and d2.line_type_lookup_code = 'ITEM'
and d2.invoice_id = d.invoice_id) ACTUAL_AMOUNT*/

from ap_invoice_distributions_All d,
AP_INVOICE_LINES_All L,
ap_invoices_all g,
ap_suppliers b,
AP_INVOICE_PAYMENTS_ALL c,
ap_checks_all e,
gl_code_combinations gc
WHERE D.INVOICE_ID = L.INVOICE_ID
AND D.INVOICE_LINE_NUMBER = L.LINE_NUMBER
and g.INVOICE_ID = L.INVOICE_ID
and g.INVOICE_ID = d.INVOICE_ID
and g.INVOICE_ID = c.INVOICE_ID
and l.INVOICE_ID = c.INVOICE_ID
and d.INVOICE_ID = c.INVOICE_ID
and e.CHECK_ID = c.CHECK_ID
and gc.CODE_COMBINATION_ID = d.DIST_CODE_COMBINATION_ID
-- and d.ACCOUNTING_EVENT_ID = c.ACCOUNTING_EVENT_ID
and g.ORG_ID = L.org_id
and L.org_id = d.Org_id
and l.ORG_ID = c.ORG_ID
and c.ORG_ID = g.org_id
and e.ORG_ID = c.ORG_ID
and b.VENDOR_ID = g.VENDOR_ID
and g.org_id = 376
and c.REVERSAL_FLAG = 'N'
and trunc(e.CLEARED_DATE) between '01-DEC-2016' and '31-DEC-2017'
--and g.INVOICE_ID=115693
-- and g.GL_DATE between '01-JUN-2017' and '30-JUN-2017'
--and d.invoice_id = 94273 -- 3511993--3547454

/* select sum(NVL(base_amount,amount)) from ap_invoice_distributions_all --4308
where org_id=376
and line_type_lookup_code = 'AWT'
*/

--------------------------------------------------------------------------------------------------------------------------

select NVL(n.base_amount, n.amount) Amount
from AP_INVOICE_PAYMENTS_ALL k,
ap_invoice_distributions_all n --778
,
ap_checks_all x
where k.INVOICE_ID = n.INVOICE_ID
and k.CHECK_ID = x.CHECK_ID
and k.org_id = x.org_id
and k.org_id = n.org_id
and k.ORG_ID = 376 --108478
and k.REVERSAL_FLAG = 'N'

and x.CLEARED_DATE between '01-DEC-2016' and '31-DEC-2017'
--and k.INVOICE_ID = 115693
and n.line_type_lookup_code = 'AWT'

Thursday, 8 June 2017

What is the difference between 'Apply' check box" and 'Prepayment On Invoice' check box?

http://aporaclepayables.blogspot.com/2017/06/what-is-difference-between-apply-check.html

Let us discuss the issue with the help of an example.

Prepayment Invoice -> amount=1000
Standard Invoice -> amount=10000

Apply check box: this is checked when the Prepayment amount has been paid and Supplier sends a Standard Invoice for 10000 (not deducting the Prepayment amount). Then when trying to apply the Prepayment invoice, check the 'Apply' check box.
Using the example:
Invoice Header will be 10000
Invoice Line line will be 10000
Invoice Distributions will be 10000
On applying prepayment: the prepay line for 1000 is created.

Prepayment On Invoice check box: checked when the Prepayment has been paid and Supplier sends a Standard Invoice for 9000 (deducting the Prepayment amount ). Then when trying to apply the Prepayment to Standard Invoice, check the 'Prepayment On Invoice' check box.
Using the example:
Invoice Header is 9000
Invoice Line is for 10000
Invoice Distributions are for 10000

Friday, 21 April 2017

Tax Codes Tables Joining

http://aporaclepayables.blogspot.com/2017/04/tax-codes-tables-joining.html

select b.NAME Group_Name,
c.TAX_NAME Tax_code,
c.TAX_RATE,
d.ATTRIBUTE5 Section,
d.ATTRIBUTE3 Tax_Regime,
d.ATTRIBUTE4 IRIS_TAX_CODES,
d.ATTRIBUTE6 Exemption_Code_Blank_Space,
b.DESCRIPTION Group_Description,
d.DESCRIPTION Tax_name_Description,
c.START_DATE,
f.VENDOR_SITE_CODE Site,
d.ATTRIBUTE1 DFF_SECTION,
d.ATTRIBUTE2 DFF_DESCRIPTION,
gc.SEGMENT1 Company,
gc.SEGMENT2 Location,
gc.SEGMENT4 Account
from AP_AWT_GROUP_TAXES_ALL a,
Ap_Tax_Codes_All d,
Ap_Awt_Tax_Rates_All c,
AP_AWT_GROUPS b,
ap_supplier_sites_all f,
gl_code_combinations gc
where d.NAME = a.TAX_NAME
and d.ORG_ID = a.ORG_ID
and a.TAX_NAME = c.TAX_NAME
and c.TAX_NAME = d.NAME
and c.ORG_ID = a.ORG_ID
and f.VENDOR_SITE_ID = d.AWT_VENDOR_SITE_ID
and b.GROUP_ID = a.GROUP_ID
and f.VENDOR_ID = d.AWT_VENDOR_ID
and gc.CODE_COMBINATION_ID = d.TAX_CODE_COMBINATION_ID
and c.RATE_TYPE = 'STANDARD'
and d.ENabLED_FLAG = 'Y'
and c.END_DATE is null
order by group_name

Monday, 13 March 2017

Dual Dates Transactions

http://aporaclepayables.blogspot.com/2017/03/dual-dates-transactions.html

select distribution_line_number,pp.LINE_TYPE_LOOKUP_CODE,
kk.DOC_sequence_value,
kk.INVOICE_NUM,
kk.ORG_ID,
kk.GL_DATE,
kk.INVOICE_ID,
kk.Header_DATE,
pp.Distribution_DATE,
u.user_name dist_user,
kk.CANCELLED_DATE
from (select aia.DOC_sequence_value,
aia.INVOICE_NUM,
aia.ORG_ID,
aia.GL_DATE,
aia.INVOICE_ID, aia.cancelled_date,
aia.GL_DATE Header_DATE
from ap_invoices_all aia) kk,
(select aida.distribution_line_number, aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,aida.org_id, aida.created_by,
aida.INVOICE_ID, aida.accrual_posted_flag,
aida.ACCOUNTING_DATE Distribution_DATE
from ap_invoice_distributions_all aida) pp, fnd_user u
where kk.invoice_id = pp.invoice_id
and pp.org_id = kk.org_id
and u.USER_ID = pp.created_by
and kk.org_id = :P_ORG_ID
and kk.gl_date between NVL(:P_FROM_DATE,kk.gl_date ) and NVL(:P_TO_DATE,kk.gl_date )
and Header_DATE <> Distribution_DATE
and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
and kk.CANCELLED_DATE is null
--and kk.gl_date > '01-JAN-16'
order by kk.doc_sequence_value desc

Friday, 17 February 2017

To See Partial Payments

http://aporaclepayables.blogspot.com/2017/02/to-see-partial-payments.html

select distinct c.DOC_SEQUENCE_VALUE APV,
b.DOC_SEQUENCE_VALUE APN,
c.CHECK_DATE,
d.USER_NAME,
decode(b.PAYMENT_STATUS_FLAG,
'P',
'Partially Paid',
b.PAYMENT_STATUS_FLAG) status,
a.ORG_ID
from ap_invoice_payments_all a,
ap.ap_invoices_all# b,
ap_checks_all c,
fnd_user d
where a.INVOICE_ID = b.INVOICE_ID
and a.CHECK_ID = c.CHECK_id
and d.USER_ID = c.CREATED_BY
and a.ORG_ID = c.ORG_ID
and a.ORG_ID = b.ORG_ID
and b.payment_status_flag = 'P'
and c.CHECK_DATE > '01-JAN-2016'

Monday, 30 January 2017

How to Open DFF on Create Supplier Page

http://aporaclepayables.blogspot.com/2017/01/how-to-open-dff-on-create-supplier-page.html

Clear Cache after Compiling

Saturday, 21 January 2017

Invoice Payment Status Query

http://aporaclepayables.blogspot.com/2017/01/invoice-payment-status-query.html

select aia.DOC_SEQUENCE_VALUE,aia.INVOICE_NUM,
aia.DESCRIPTION,
aia.GL_DATE,
aia.INVOICE_DATE,
aia.invoice_amount,
-- NVL(ai.AMOUNT_REMAINING,aia.AMOUNT_PAID) Total_Disp,
coalesce(ai.AMOUNT_REMAINING,aia.amount_paid, aia.invoice_amount) Total_Disp,
-- decode (ai.AMOUNT_REMAINING, null,aia.AMOUNT_PAID, null, aia.INVOICE_AMOUNT),
aia.payment_status,
al.SEGMENT1 Supplier_number,
al.VENDOR_NAME,
ala.VENDOR_SITE_CODE
from ap_invoices_V aia, ap_suppliers al, ap_supplier_sites_all ala, ap_invoices_ready_to_pay_v ai
where aia.VENDOR_ID = al.VENDOR_ID
and aia.VENDOR_SITE_ID = ala.VENDOR_SITE_ID
and aia.ORG_ID = ala.ORG_ID
and ai.INVOICE_ID(+)=aia.INVOICE_ID
and ai.ORG_ID(+)=aia.ORG_ID
and aia.gl_date between '01-JUL-2016' and '31-DEC-2016' --Date Paramteres

Friday, 20 January 2017

Withholding Tax Inquiry

http://aporaclepayables.blogspot.com/2017/01/withholding-tax-inquiry.html

select kk.INVOICE_NUM,gg.NAME,tt.LINE_TYPE_LOOKUP_CODE from ap_invoices_all kk, ap_invoice_payments_all qq, ap_awt_groups gg
, ap_invoice_distributions_all tt
where kk.INVOICE_ID = tt.INVOICE_ID
and qq.INVOICE_ID = kk.INVOICE_ID
and tt.INVOICE_ID = qq.INVOICE_ID
and gg.GROUP_ID = tt.PAY_AWT_GROUP_ID
and qq.CHECK_ID = 69884
and qq.ORG_ID = kk.ORG_ID
and tt.ORG_ID =qq.org_id
and tt.ORG_ID = 185

Thursday, 19 January 2017

AP Aging Query

http://aporaclepayables.blogspot.com/2017/01/ap-aging-query.html

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S', '82');
end;

--------------------------------------------------------------

SELECT distinct i.invoice_num, i.invoice_date, k.invoice_amount, k.doc_sequence_value, k.vendor_name
,DECODE
(apps.ap_invoices_pkg.get_approval_status
(i.invoice_id,
i.invoice_amount,
i.payment_status_flag,
i.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'

) invoice_status, i.payment_status_flag "Payment Status", --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
k.posting_flag "Invoice Accounting Status" --'Y' -- Accounted, 'P' -- Partial , 'N'-- No Accounted
FROM ap_invoices_all i, Ap_Invoices_v k

where 1=1
and i.invoice_id=k.invoice_id
AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.invoice_id not in(

select P.INVOICE_ID
from ap_invoice_payments_all p, AP_PAYMENT_SCHEDULES_ALL PS , AP_CHECKS_ALL C
WHERE
P.CHECK_ID=C.CHECK_ID AND
P.INVOICE_ID=PS.INVOICE_ID
AND (PS.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or p.reversal_flag = 'N'
)
and c.cleared_date<='31-DEC-2016' -- added for hubco
and p.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and ps.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and c.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and p.org_id=ps.org_id -- ADDED ON 31-JUL-15
and p.org_id=c.org_id -- ADDED ON 31-JUL-15

)
-- and i.PAYMENT_STATUS_FLAG <> 'Y'
-- and k.vendor_name = 'Pakistan State Oil Company Limited'
and i.INVOICE_DATE <= '31-DEC-2016'
-- and i.invoice_id=69912

--------------------------------------------------------------------------------------------------------------------------

SELECT distinct i.invoice_num, i.invoice_date, k.invoice_amount, k.doc_sequence_value, k.vendor_name
,DECODE
(apps.ap_invoices_pkg.get_approval_status
(i.invoice_id,
i.invoice_amount,
i.payment_status_flag,
i.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'

) invoice_status, i.payment_status_flag "Payment Status", --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
k.posting_flag "Invoice Accounting Status" --'Y' -- Accounted, 'P' -- Partial , 'N'-- No Accounted
FROM ap_invoices_all i, Ap_Invoices_v k

where 1=1
and i.invoice_id=k.invoice_id
AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.INVOICE_ID not in (

SELECT a.invoice_id

FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.ap_payment_schedules_all d,
apps.ap_invoice_payments_all ap,
ap_checks_all ac,
Ap_Invoices_v al,
ap_checks_v asd
WHERE a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND b.vendor_id = c.vendor_id
AND a.invoice_id = d.invoice_id
AND ap.invoice_id = a.invoice_id
AND ac.CHECK_ID = ap.CHECK_ID
and ac.check_id=asd.check_id
and ap.check_id=asd.check_id
and a.invoice_id=al.invoice_id
and d.invoice_id=al.invoice_id
AND (d.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or ap.reversal_flag = 'N')
and ac.cleared_date<='31-MAR-2016'
)
-- and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
-- AND a.org_id =?
--and ac.check_number='?'
--and a.invoice_id= 1234
--AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
-- AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')]
/* AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.invoice_id not in(

select P.INVOICE_ID
from ap_invoice_payments_all p, AP_PAYMENT_SCHEDULES_ALL PS
, AP_CHECKS_ALL C
WHERE
P.CHECK_ID=C.CHECK_ID AND
P.INVOICE_ID=PS.INVOICE_ID
AND (PS.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or p.reversal_flag = 'N'
)
and c.cleared_date<='31-MAR-2016' -- added for hubco
and p.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and ps.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and c.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and p.org_id=ps.org_id -- ADDED ON 31-JUL-15
and p.org_id=c.org_id -- ADDED ON 31-JUL-15

) */
-- and i.PAYMENT_STATUS_FLAG <> 'Y'
-- and k.vendor_name = 'Pakistan State Oil Company Limited'
and i.INVOICE_DATE <= '31-MAR-2016'
-- and i.invoice_id=69912