Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 29 August 2019

Query to get Supplier Payment Details in Oracle apps R12


SELECT   AP.VENDOR_NAME VENDOR_NAME,
         AP.SEGMENT1 VENDOR_NUM,
         AP.ORGANIZATION_TYPE_LOOKUP_CODE ORGANIZATION_TYPE,
         AP.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,
         OU.NAME LOCATION,
         APS.VENDOR_SITE_CODE VENDOR_SITE,
         APS.ADDRESS_LINE1 ADDRESS1,
         APS.ADDRESS_LINE2 ADDRESS2,
         APS.ADDRESS_LINE3 ADDRESS3,
         APS.CITY CITY,
         APS.STATE STATE,
         APS.ZIP ZIP,
         APS.COUNTRY COUNTRY,
         AP.TAX_REPORTING_NAME TAX_REPORTING_NAME,
         APS.TAX_REPORTING_SITE_FLAG TAX_REPORTING_SITE_FLAG,
         TO_CHAR(ACA.CHECK_DATE,'DD-MON-YYYY')   CHECK_DATE,
         ACA.AMOUNT AMOUNT_PAID,
         ACA.STATUS_LOOKUP_CODE,
FROM     AP_SUPPLIERS AP,
         AP_SUPPLIER_SITES_ALL APS,
         AP_CHECKS_ALL ACA,
         HR_OPERATING_UNITS OU
WHERE    1 = 1
AND      AP.VENDOR_ID = APS.VENDOR_ID
AND      APS.ORG_ID = OU.ORGANIZATION_ID
AND      ACA.VENDOR_ID(+) = APS.VENDOR_ID
AND      ACA.VENDOR_SITE_ID(+) = APS.VENDOR_SITE_ID
GROUP BY AP.VENDOR_NAME,
         AP.VENDOR_TYPE_LOOKUP_CODE,
         OU.NAME,
         APS.VENDOR_SITE_CODE,
         APS.VENDOR_SITE_ID,
         APS.ADDRESS_LINE1,
         APS.ADDRESS_LINE2,
         APS.ADDRESS_LINE3,
         APS.CITY,
         APS.STATE,
         APS.ZIP,
         APS.COUNTRY,
         AP.TAX_REPORTING_NAME,
         AP.ORGANIZATION_TYPE_LOOKUP_CODE,
         AP.SEGMENT1,
         APS.TAX_REPORTING_SITE_FLAG,
         ACA.STATUS_LOOKUP_CODE,
         ACA.CHECK_DATE,
         ACA.AMOUNT
ORDER BY AP.VENDOR_NAME, ACA.CHECK_DATE DESC

Share this article :

0 comments:

Post a comment