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

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
 

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

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