Thursday, 19 November 2015

Query AR Invoice Aging Query

SELECT  pa.segment1 project_number,
       pa.name project_name,
       XX_GET_AGREEMENT_Num( pa.project_id) agreement_number,
      ppc.customer_name,
     (CASE WHEN ppc.customer_id  in (4046,12040,5040,5044) THEN 'Internal'
      ELSE 'External'        
      END) Internal_Or_External,
     (SELECT FLEX_VALUE_MEANING
     FROM FND_FLEX_VALUES_VL
     WHERE FLEX_VALUE_SET_ID=1015618
     AND DESCRIPTION IN (SELECT NAME FROM HR_ORGANIZATION_UNITS
     WHERE organization_id =pa.carrying_out_organization_id)
      ) cost_center,
      (SELECT NAME FROM hr_organization_units WHERE organization_id =pa.carrying_out_organization_id ) Business_unit,
       rcta.trx_number invoice_number,
       TRUNC(rcta.trx_date) invoice_date,
       rcta.term_due_date invoice_due_date,
       apsa.AMOUNT_DUE_REMAINING outstanding_amount,
        (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
               WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN  (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN  (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120  THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
        END) DAYS,
       apsa.AMOUNT_DUE_REMAINING current_1,
       (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END)past_Due_1_30,
      (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
        END) past_due_31_60,
         (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END) past_due_61_90,
      (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END) past_due_91_120,
       (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
        END) past_due_121_Plus
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       AR_PAYMENT_SCHEDULES_ALL apsa,
       PA_PROJECTS_ALL PA,
       PA_PROJECT_CUSTOMERS_V ppc
     --  PA_AGREEMENTS_ALL paa,
     --  PA_PROJECT_FUNDINGS ppf  
WHERE 1=1
--AND RCTA.TRX_NUMBER='100000'
AND RCTA.STATUS_TRX='OP'
AND rcta.customer_trx_id =rctla.customer_trx_id
AND apsa.customer_trx_id =rcta.customer_trx_id
AND apsa.customer_trx_id  =rctla.customer_trx_id
AND pa.segment1= rcta.interface_header_attribute1
AND ppc.project_id =pa.project_id
AND rcta.sold_to_customer_id =ppc.customer_id
--AND paa.agreement_id =ppf.agreement_id
--AND ppc.customer_id = paa.customer_id
--AND substr(paa.description,2,5)=pa.segment1
 ORDER BY  pa.segment1 ASC;

No comments:

Post a Comment