Thursday 18 April 2013

AR Laber chage based on the Customer

SELECT   CUSTOMER_NAME,
           NVL (SUM (Amount), 0) charges_Amount,
           --TDS_Individuals_Customers_Name
           CASE
              WHEN TDS_Individuals_Customers_Name LIKE 'TDS Re%'
              THEN
                ( select amount from RA_CUST_TRX_LINE_GL_DIST_ALL where code_combination_id=x.code_combination_id  and CUSTOMER_TRX_ID = x.CUSTOMER_TRX_ID)
              ELSE
                 0
           END
              AS TDS_Individuals_Customers_Name,
           TDS_Amount AS TDS_Amount
    FROM   (                  
     SELECT 
       RACTA.CUSTOMER_TRX_ID ,gcc.code_combination_id,
         arc.CUSTOMER_NAME,
                     (RACTLA.UNIT_SELLING_PRICE * RACTLA.QUANTITY_ORDERED)
                        AS Amount,
                     glcdv.account AS TDS_Individuals_Customers_Name,
                     NVL (jaitdsc.amount, 0) AS TDS_Amount
              FROM   RA_CUSTOMER_TRX_ALL RACTA,
                     ra_customer_trx_lines_all RACTLA,
                     RA_CUST_TRX_LINE_GL_DIST_ALL RACTLGLDA,
                     GL_CODE_COMBINATIONS GCC,
                     ar_customers arc,
                     hr_operating_units hou,
                     JAI_AR_TDS_CERTIFICATES jaitdsc,
                     xxcapl_gl_code_desc_v glcdv
            WHERE   1 = 1
                     AND RACTA.CUSTOMER_TRX_ID = RACTLA.CUSTOMER_TRX_ID
                     AND RACTLA.CUSTOMER_TRX_LINE_ID =
                           RACTLGLDA.CUSTOMER_TRX_LINE_ID
                     AND RACTLGLDA.CODE_COMBINATION_ID =
                           GCC.CODE_COMBINATION_ID
                     AND RACTA.BILL_TO_CUSTOMER_ID = arc.CUSTOMER_ID
                     AND RACTLGLDA.account_class = 'REV'
                     AND RACTLA.line_type = 'LINE'
                     AND hou.organization_id = RACTA.org_id
                     AND jaitdsc.CUSTOMER_ID(+) = arc.CUSTOMER_ID
                     AND glcdv.code_combination_id = gcc.code_combination_id
                      and RACTA.COMPLETE_FLAG='Y'                
                    --AND RACTA.CUSTOMER_TRX_ID =               -- 112005
                     AND RACTA.org_id = NVL (:P_ORG_ID, RACTA.org_id)
                     AND TRUNC (RACTA.trx_date) >= :p_from_date
                     AND TRUNC (RACTA.trx_date) <= :p_to_date
                     AND RACTA.bill_to_customer_id =
                           NVL (:p_customer_id, RACTA.bill_to_customer_id)                                                                                                      
                                                     ) X
GROUP BY   X.CUSTOMER_NAME, X.TDS_Amount, X.TDS_Individuals_Customers_Name,
 x.CUSTOMER_TRX_ID ,x.code_combination_id

No comments:

Post a Comment