ADVANCE TO SUPPLIER AGEING QUERY

  SELECT 

    vendor_name,

    supplier_num,

    SUM("current1") AS "current",

     SUM("1-30 Days") AS "1-30 Days",

     SUM("30-60 days") AS "30-60 days",

     SUM("60-90 Days") AS "60-90 Days",

     SUM("90-180 days") AS "90-180 days",

     SUM("180-365 days") AS "180-365 days",

     SUM("365-730 days") AS "365-730 days",

     SUM("730 above") AS "730 above",

     sum("current"+"1-30 Days"+"30-60 days"+"60-90 Days"+"90-180 days"+"180-365 days"+"365-730 days"+"730 above") BALANCE


FROM

    (

        SELECT

         lineS.line_number,

            MAX(ps.vendor_name) vendor_name,

           MAX( ps.segment1 )supplier_num,

            inv.invoice_num,

            MAX(lines.line_TYPE_LOOKUP_CODE ) line_TYPE_LOOKUP_CODE,

           MAX( CASE

                WHEN to_char(dist1.accounting_date, 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD') THEN lines.amount

                ELSE 0

            END) AS "current1",

           MAX( CASE

                WHEN floor(sysdate -dist1.accounting_date) > 0 AND floor(sysdate - dist1.accounting_date) <= 30 THEN lines.amount

                ELSE 0

            END) AS "1-30 Days",

           MAX(  CASE

                WHEN floor(sysdate - dist1.accounting_date) > 30 AND floor(sysdate -dist1.accounting_date) <= 60 THEN lines.amount

                ELSE 0

            END) AS "30-60 days",

          MAX(   CASE

                WHEN floor(sysdate - dist1.accounting_date) > 60 AND floor(sysdate - dist1.accounting_date) <= 90 THEN lines.amount

                ELSE 0

            END) AS "60-90 Days",

           MAX(  CASE

                WHEN floor(sysdate - dist1.accounting_date) > 90 AND floor(sysdate -dist1.accounting_date) <= 180 THEN lines.amount

                ELSE 0

            END) AS "90-180 days",

           MAX(  CASE

                WHEN floor(sysdate - dist1.accounting_date) > 180 AND floor(sysdate - dist1.accounting_date) <= 365 THEN lines.amount

                ELSE 0

            END) AS "180-365 days",

            MAX( CASE

                WHEN floor(sysdate - dist1.accounting_date) > 365 AND floor(sysdate - dist1.accounting_date) <= 730 THEN lines.amount

                ELSE 0

            END) AS "365-730 days",

            MAX( CASE

                WHEN floor(sysdate - dist1.accounting_date) > 730 THEN lines.amount

                ELSE 0

            END) AS "730 above"

        FROM 

            ap_invoice_lines_all lines,

            ap_invoices_all inv,

            poz_suppliers_v ps,

            AP_INVOICE_PAYMENTS_ALL dist1,

            (SELECT MAX(gcc.segment2) segment2, DIST.INVOICE_ID,MAX(dist.invoice_line_number) invoice_line_number  FROM    gl_code_combinations gcc,    ap_invoice_distributions_all dist WHERE  

             dist.dist_code_combination_id = gcc.code_combination_id GROUP BY dist.invoice_line_number,DIST.INVOICE_ID

         ) GCC1      

        WHERE 

             dist1.invoice_id = inv.invoice_id

            AND lines.invoice_id = inv.invoice_id

            AND GCC1.invoice_id = inv.invoice_id

               AND GCC1.invoice_line_number = lineS.line_number

            AND inv.vendor_id = ps.vendor_id

            AND ((INV.INVOICE_DATE) <= NVL((:Payment_Date), ((dist1.accounting_date)))

                 OR (INV.INVOICE_DATE) >= nvl((:Payment_Date), ((dist1.accounting_date))))

            AND ps.vendor_name = NVL(:Supplier_name, ps.vendor_name)

            AND ps.SEGMENT1 = NVL(:Supplier_number, ps.SEGMENT1)

            AND inv.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

             AND gcc1.segment2 = '1161xxxxx1'

 GROUP BY        inv.invoice_num,  lineS.line_number

    ) 

GROUP BY vendor_name, supplier_num


Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more