Oracle Payables - Oracle EBS R12

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

Tuesday, 20 December 2016

FRM-40735 POST-insert trigger raised unhandeled expression exception ORA-12899 while saving New tax code

http://aporaclepayables.blogspot.com/2016/12/frm-40735-post-insert-trigger-raised.html

On R&D it’s found that Description allows 80 characters only & exceeding from this limit, system prompts the error message which we were facing.

Monday, 14 November 2016

oracle.apps.iby.exception.PSException: Error: bad data - the instruction status is invalid.

http://aporaclepayables.blogspot.com/2016/11/oracleappsibyexceptionpsexception-error.html

+---------------------------------------------------------------------------+
Payments: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

IBY_FD_PAYMENT_FORMAT: Format Payment Instructions
+---------------------------------------------------------------------------+

Current system time is 14-NOV-2016 12:15:25

+---------------------------------------------------------------------------+

FDExtractAndFormatting->runProgram():TEST
RequestId:(TEST)1721435
Start time:(TEST) Mon Nov 14 12:15:28 PKT 2016
Before calling OraPmt.init()::(TEST)
After calling OraPmt.init()
Before setparams - m_instructionID : null
FDExtractAndFormatting->Setting Parameters
Enter: iby.scheduler.FDExtractAndFormatting.setParameters()
Param name: InstructionReferenceNumber value: 53725
Param name: ReprintFlag, value: N
FDExtractAndFormatting->Set Parameters ...
After setparams - m_instructionID : 53725
Enter: iby.scheduler.FDExtractAndFormatting.lockInstruction()
Executing BEGIN IBY_DISBURSE_UI_API_PUB_PKG.lock_pmt_entity(:1, :2, :3, :4); END;
Successfully executed PL/SQL.
Exit: iby.scheduler.FDExtractAndFormatting.lockInstruction()
After lockInstruction()
Before runmainlogic - m_instructionID : 53725
Enter: iby.scheduler.FDExtractAndFormatting.runMainLogic()
Enter: Mon Nov 14 12:15:29 PKT 2016
Before load FD profile - m_instructionID : 53725
After load FD profile - m_instructionID : 53725
Enter: iby.payment.FundDisbursementProfile.load
Preparing to load profile. instructionID := 53725
Found profile. parsing records.
Done parsing. Instruction records : FundDisbursementProfile{instructionID:=53725,instruction_status:=TERMINATED,instruction process_type:=IMMEDIATE,processing_type:=PRINTED,electronic_processing_channel:=null,transmit_immediate_flag:=N,print_immediate_flag:=N,printer_name:=null,payment_format:=Format{formatCode:=IBY_PAY_CHK_STANDARD_1,formatType:=OUTBOUND_PAYMENT_INSTRUCTION,templateCode:=IBYDC_STD1,defaultOutputType:=null,locale:=en_00,extract:=Extract{extractCode:=IBY_FD_PAYMENT_INSTRUCTION,extractVersion:=1}} ,transmit_configuration:=null ,default_output_type:=null }
Exit: iby.payment.FundDisbursementProfile.load
Payment process profile successfully loaded.
oracle.apps.iby.exception.PSException: Error: bad data - the instruction status is invalid.
 at oracle.apps.iby.scheduler.FDExtractAndFormatting.runMainLogic(FDExtractAndFormatting.java:350)
 at oracle.apps.iby.scheduler.FDExtractAndFormatting.runProgram(FDExtractAndFormatting.java:159)
 at oracle.apps.fnd.cp.request.Run.main(Run.java:156)

Error: bad data - the instruction status is invalid.
An error occurred. Roll back any changes.
Done roll back changes.
Request completed in error.
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
No completion options were requested.

Output file size: 
0

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 14-NOV-2016 12:15:29

+---------------------------------------------------------------------------+

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

This happens when User Creates Payment and in a second he Voids the Payment.

SELECT  *
  FROM   IBY_PAY_INSTRUCTIONS_ALL
 WHERE   payment_instruction_status = 'TERMINATED'
 order by creation_date desc