eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JA_JAINDTBR_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21


      SELECT
        SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
        SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
               ,1))
      FROM
        RA_CUSTOMER_TRX_ALL A,
        AR_PAYMENT_SCHEDULES_ALL C,
        RA_CUST_TRX_LINE_GL_DIST_ALL B
      WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
        AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
        AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
        AND C.GL_DATE <= TRUNC(P_START_DATE)
        AND A.INVOICE_CURRENCY_CODE = CURR_CODE
        AND A.COMPLETE_FLAG = 'Y'
        AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
        AND A.ORG_ID = NVL(P_ORGANIZATION_ID
         ,A.ORG_ID)
        AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
        AND B.LATEST_REC_FLAG = 'Y'
        AND C.PAYMENT_SCHEDULE_ID IN (
        SELECT
          MIN(PAYMENT_SCHEDULE_ID)
        FROM
          AR_PAYMENT_SCHEDULES_ALL
        WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );

Line: 47


      SELECT
        SUM(A.AMOUNT) SUM_AMOUNT,
        SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
               ,1.00)) SUM_AMOUNT_EXCHANGE
      FROM
        AR_CASH_RECEIPTS_ALL A
      WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
        AND A.ORG_ID = NVL(P_ORGANIZATION_ID
         ,A.ORG_ID)
        AND A.CURRENCY_CODE = CURR_CODE
        AND EXISTS (
        SELECT
          1
        FROM
          AR_CASH_RECEIPT_HISTORY_ALL
        WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
          AND ORG_ID = NVL(P_ORGANIZATION_ID
           ,A.ORG_ID)
          AND GL_DATE <= TRUNC(P_START_DATE) );

Line: 67


      SELECT
        SUM(A.AMOUNT) SUM_AMOUNT,
        SUM(A.AMOUNT * NVL(A.EXCHANGE_RATE
               ,1.00)) SUM_AMOUNT_EXCHANGE
      FROM
        AR_CASH_RECEIPTS_ALL A,
        AR_CASH_RECEIPT_HISTORY_ALL B
      WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
        AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
        AND B.GL_DATE <= TRUNC(P_START_DATE)
        AND B.STATUS = LV_REV_STATUS
        AND A.REVERSAL_DATE is not null
        AND A.ORG_ID = NVL(P_ORGANIZATION_ID
         ,A.ORG_ID)
        AND A.CURRENCY_CODE = CURR_CODE;

Line: 83


      SELECT
        NVL(SUM(ABS(NVL(D.EARNED_DISCOUNT_TAKEN
                       ,0)))
           ,0) SUM_AMOUNT,
        NVL(SUM(ABS(NVL(D.ACCTD_EARNED_DISCOUNT_TAKEN
                       ,0)))
           ,0) SUM_AMOUNT_EXCHANGE
      FROM
        RA_CUSTOMER_TRX_ALL B,
        AR_RECEIVABLE_APPLICATIONS_ALL D
      WHERE B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
        AND B.COMPLETE_FLAG = 'Y'
        AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE)
        AND D.APPLIED_CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
        AND B.INVOICE_CURRENCY_CODE = CURR_CODE
        AND D.EARNED_DISCOUNT_TAKEN is not null
        AND D.EARNED_DISCOUNT_TAKEN <> 0
        AND B.ORG_ID = NVL(P_ORGANIZATION_ID
         ,B.ORG_ID)
        AND D.APPLICATION_TYPE = CP_APP_TYPE
        AND D.DISPLAY = 'Y';

Line: 105


      SELECT
        SUM(A.AMOUNT),
        SUM(A.AMOUNT * NVL(B.EXCHANGE_RATE
               ,1.00)) SUM_AMOUNT_EXCHANGE
      FROM
        AR_ADJUSTMENTS_ALL A,
        AR_CASH_RECEIPTS_ALL B
      WHERE A.ASSOCIATED_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
        AND B.PAY_FROM_CUSTOMER = CUSTOMER_ID
        AND A.GL_DATE <= TRUNC(P_START_DATE)
        AND B.ORG_ID = NVL(P_ORGANIZATION_ID
         ,B.ORG_ID)
        AND B.CURRENCY_CODE = CURR_CODE;

Line: 119


      SELECT
        SUM(B.AMOUNT),
        SUM(B.AMOUNT * NVL(C.EXCHANGE_RATE
               ,1.00)) SUM_AMOUNT_EXCHANGE
      FROM
        AR_ADJUSTMENTS_ALL B,
        RA_CUSTOMER_TRX_ALL C,
        AR_PAYMENT_SCHEDULES_ALL D,
        GL_CODE_COMBINATIONS E
      WHERE B.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
        AND C.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
        AND B.GL_DATE <= TRUNC(P_START_DATE)
        AND E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
        AND B.PAYMENT_SCHEDULE_ID = D.PAYMENT_SCHEDULE_ID
        AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
        AND B.STATUS = 'A'
        AND C.ORG_ID = NVL(P_ORGANIZATION_ID
         ,B.ORG_ID)
        AND C.INVOICE_CURRENCY_CODE = CURR_CODE;

Line: 139


      SELECT
        SUM(E.AMOUNT_CR) SUM_AMOUNT,
        SUM(E.ACCTD_AMOUNT_CR) SUM_EXCHANGE_AMOUNT
      FROM
        RA_CUSTOMER_TRX_ALL B,
        AR_CASH_RECEIPTS_ALL C,
        AR_RECEIVABLE_APPLICATIONS_ALL D,
        AR_DISTRIBUTIONS_ALL E
      WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
        AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
        AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
        AND B.ORG_ID = NVL(P_ORGANIZATION_ID
         ,B.ORG_ID)
        AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
        AND B.INVOICE_CURRENCY_CODE = CURR_CODE
        AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
        AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE);

Line: 157


      SELECT
        SUM(E.AMOUNT_DR) SUM_AMOUNT,
        SUM(E.ACCTD_AMOUNT_DR) SUM_EXCHANGE_AMOUNT
      FROM
        RA_CUSTOMER_TRX_ALL B,
        AR_CASH_RECEIPTS_ALL C,
        AR_RECEIVABLE_APPLICATIONS_ALL D,
        AR_DISTRIBUTIONS_ALL E
      WHERE B.CUSTOMER_TRX_ID = D.APPLIED_CUSTOMER_TRX_ID
        AND C.CASH_RECEIPT_ID = D.CASH_RECEIPT_ID
        AND E.SOURCE_ID = D.RECEIVABLE_APPLICATION_ID
        AND B.ORG_ID = NVL(P_ORGANIZATION_ID
         ,B.ORG_ID)
        AND B.INVOICE_CURRENCY_CODE = CURR_CODE
        AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
        AND TRUNC(D.GL_DATE) <= TRUNC(P_START_DATE)
        AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE );

Line: 197


      SELECT
        SUM(C.AMOUNT_APPLIED) SUM_AMOUNT,
        SUM(C.AMOUNT_APPLIED * NVL(A.EXCHANGE_RATE
               ,1.00)) SUM_AMOUNT_EXCHANGE
      FROM
        AR_CASH_RECEIPTS_ALL A,
        AR_CASH_RECEIPT_HISTORY_ALL B,
        AR_RECEIVABLE_APPLICATIONS_ALL C
      WHERE A.PAY_FROM_CUSTOMER = CUSTOMER_ID
        AND TRUNC(B.GL_DATE) <= TRUNC(P_START_DATE)
        AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
        AND A.CASH_RECEIPT_ID = C.CASH_RECEIPT_ID
        AND C.CASH_RECEIPT_HISTORY_ID = B.CASH_RECEIPT_HISTORY_ID
        AND C.APPLIED_PAYMENT_SCHEDULE_ID = - 3
        AND C.STATUS = LV_ACT_STATUS
        AND A.CURRENCY_CODE = CURR_CODE
        AND B.REVERSAL_GL_DATE IS NULL
        AND B.CURRENT_RECORD_FLAG = 'Y'
        AND A.ORG_ID = NVL(P_ORGANIZATION_ID
         ,A.ORG_ID)
        AND not exists (
        SELECT
          1
        FROM
          AR_CASH_RECEIPT_HISTORY_ALL
        WHERE CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
          AND STATUS = LV_REV_STATUS );

Line: 225


    SELECT
      SUM((B.AMOUNT)) SUM_EXT_AMOUNT,
      SUM((B.AMOUNT) * NVL(A.EXCHANGE_RATE
             ,1))
    INTO V_CRE_MEMO_AMT,V_CRE_MEMO_FUNC_AMT
    FROM
      RA_CUSTOMER_TRX_ALL A,
      AR_PAYMENT_SCHEDULES_ALL C,
      RA_CUST_TRX_LINE_GL_DIST_ALL B
    WHERE A.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
      AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
      AND C.CLASS In ( LV_CM_CLASS )
      AND C.GL_DATE <= TRUNC(P_START_DATE)
      AND A.INVOICE_CURRENCY_CODE = CURR_CODE
      AND A.COMPLETE_FLAG = 'Y'
      AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
      AND A.ORG_ID = NVL(P_ORGANIZATION_ID
       ,A.ORG_ID)
      AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
      AND C.PAYMENT_SCHEDULE_ID in (
      SELECT
        MIN(PAYMENT_SCHEDULE_ID)
      FROM
        AR_PAYMENT_SCHEDULES_ALL
      WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );

Line: 322


Select
        sum((b.amount)) sum_ext_amount,
        sum((b.amount) * NVL(a.exchange_rate,1))
From
        ra_customer_trx_all              A,
        ar_payment_schedules_all         C,
        ra_cust_trx_line_gl_dist_all     B
Where
        a.bill_to_customer_id   =  P_CUSTOMER_ID
AND     a.customer_trx_id       = c.customer_trx_id
AND     c.class In(lv_inv_class,lv_dm_class,lv_dep_class)--rchandan for bug#4428980
--AND     trunc(a.trx_date)      <= trunc( p_start_date)
AND     c.gl_date <= trunc( p_start_date)
AND     a.invoice_currency_code =  P_CURR_CODE
AND     a.complete_flag         = 'Y'
AND     b.customer_trx_id       = a.customer_trx_id
AND     a.org_id                = NVL( P_ORGANIZATION_ID, a.org_id)
AND     b.account_class         = lv_rec_account_class--rchandan for bug#4428980
and     b.latest_rec_flag       = 'Y'
AND     c.Payment_schedule_id
IN      (SELECT MIN(PAYMENT_SCHEDULE_ID)
         FROM   AR_PAYMENT_SCHEDULES_ALL
         WHERE  CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
        )
;

Line: 349


Select
        sum(a.amount) sum_amount,
        sum(a.amount * NVL(a.exchange_rate,1.00)) sum_amount_exchange
From
        ar_cash_receipts_all            A
Where
        a.pay_from_customer    =  P_CUSTOMER_ID
AND     a.org_id               = NVL( P_ORGANIZATION_ID, a.org_id)
AND     a.currency_code        =  P_CURR_CODE
--Added the below by Sanjikum for Bug #3962497
AND 		EXISTS (	SELECT	1
									FROM		ar_cash_receipt_history_all
									WHERE 	cash_receipt_id = a.cash_receipt_id
									AND 		org_id = NVL( P_ORGANIZATION_ID, a.org_id)
									AND 		gl_date <= trunc( p_start_date)
							 );

Line: 367


Select
        sum(a.amount) sum_amount,
        sum(a.amount * NVL(a.exchange_rate,1.00)) sum_amount_exchange
From
        ar_cash_receipts_all A ,
        ar_cash_receipt_history_all B
Where
        a.pay_from_customer =  P_CUSTOMER_ID
and     a.cash_receipt_id = b.cash_receipt_id
AND     b.gl_date          <= trunc( p_start_date)
--and     b.current_record_flag   = 'Y' --Commented by Sanjikum for Bug #3962497
AND 	 	b.status                      = lv_rev_status --Added by Sanjikum for Bug #3962497--rchandan for bug#4428980
and     a.reversal_date is not null
AND     a.org_id            = NVL( P_ORGANIZATION_ID, a.org_id) -- added by sriram
AND     a.currency_code     =  P_CURR_CODE;

Line: 386


Select
       nvl(sum(abs(NVL(d.earned_discount_taken,0))),0)  sum_amount,
       nvl(sum(abs(NVL(d.ACCTD_EARNED_DISCOUNT_TAKEN,0))),0)  sum_amount_exchange
From   ra_customer_trx_ALL             B,
       ar_receivable_applications_all  d
Where
       b.bill_to_customer_id   =  P_CUSTOMER_ID
AND    b.complete_flag         = 'Y'
AND    trunc(d.GL_DATE)       <= trunc( p_start_date)
AND    d.applied_customer_trx_id       = b.customer_trx_id
AND    b.invoice_currency_code =  P_CURR_CODE
AND    d.earned_discount_taken is not null
and    d.earned_discount_taken <> 0
AND    B.org_id                = nvl( P_ORGANIZATION_ID ,b.org_id)
and    d.application_type = cp_app_type --rchandan for bug#4428980
and    d.display = 'Y'
;

Line: 405


SELECT  SUM(A.amount),
        SUM(A.amount * NVL(b.exchange_rate,1.00)) sum_amount_exchange
FROM    ar_adjustments_all           A,
        ar_cash_receipts_all         b
WHERE   A.associated_cash_receipt_id = b.cash_receipt_id
and     b.pay_from_customer          =  P_CUSTOMER_ID
--and     trunc(a.apply_date)         <=  trunc( p_start_date)
and     A.gl_date                   <=  trunc( p_start_date)
AND     b.org_id                     = NVL( P_ORGANIZATION_ID, b.org_id)
AND     b.currency_code              =  P_CURR_CODE;

Line: 418


select  sum(b.amount),
        sum(b.amount * NVL(c.exchange_rate,1.00)) sum_amount_exchange
FROM    ar_adjustments_all          b,
        ra_customer_trx_all         c,
        ar_payment_schedules_all    d,
        gl_code_combinations        e
WHERE
        b.customer_trx_id       = c.customer_trx_id
and     c.bill_to_customer_id   =  P_CUSTOMER_ID
and     b.gl_date              <= trunc( p_start_date)
and     e.code_combination_id   = b.code_combination_id
and     b.payment_schedule_id   = d.payment_schedule_id
and     b.customer_trx_id       = d.customer_trx_id
and     b.status                = 'A'
and     c.org_id                = NVL( P_ORGANIZATION_ID, b.org_id)
and     c.invoice_currency_code =  P_CURR_CODE;

Line: 439


SELECT
        sum(e.amount_cr)             sum_amount     ,
        sum(e.acctd_amount_cr)       sum_exchange_amount
FROM    ra_customer_trx_all              b ,
        ar_cash_receipts_all             c,
        ar_receivable_applications_all   d,
        ar_distributions_all             e
WHERE   b.customer_trx_id            = d.APPLIED_CUSTOMER_TRX_ID
AND     c.cash_receipt_id            = d.cash_receipt_id
AND     e.SOURCE_ID                  = d.receivable_application_id
AND     b.org_id                     = nvl( p_organization_id,b.org_id)
AND     e.source_Type IN (lv_loss_source_Type, lv_gain_source_Type)--rchandan for bug#4428980
AND     b.invoice_currency_code      =  P_CURR_CODE
AND     b.BILL_TO_CUSTOMER_ID        =  P_CUSTOMER_ID
AND     TRUNC(d.gl_date)            <= trunc( p_start_date);

Line: 457


SELECT
        sum(e.amount_dr)             sum_amount     ,
        sum(e.acctd_amount_dr)       sum_exchange_amount
FROM    ra_customer_trx_all              b ,
        ar_cash_receipts_all             c ,
        ar_receivable_applications_all   d ,
        ar_distributions_all             e
WHERE
        b.customer_trx_id            = d.APPLIED_CUSTOMER_TRX_ID
AND     c.cash_receipt_id            = d.cash_receipt_id
AND     e.SOURCE_ID                  = d.receivable_application_id
AND     b.org_id                     = NVL( p_organization_id,b.org_id)
AND     b.invoice_currency_code      =  P_CURR_CODE
AND     b.BILL_TO_CUSTOMER_ID        =  P_CUSTOMER_ID
AND     TRUNC(d.gl_date)            <= trunc( p_start_date)
AND     e.source_Type IN (lv_loss_source_Type, lv_gain_source_Type );--rchandan for bug#4428980

Line: 504


Select
        sum(c.amount_applied) sum_amount,
        sum(c.amount_applied * NVL(a.exchange_rate,1.00)) sum_amount_exchange
From
        ar_cash_receipts_all            A,
        ar_cash_receipt_history_all     B,
        ar_receivable_applications_all  c
Where
        a.pay_from_customer =  P_CUSTOMER_ID
AND     trunc(b.gl_date)   <= trunc( p_start_date)
AND     a.cash_receipt_id   = b.cash_receipt_id
and     a.cash_receipt_id   = c .cash_receipt_id
and     c.cash_receipt_history_id = b.cash_receipt_history_id
and     c.applied_payment_schedule_id = -3
and     c.status = lv_act_status--rchandan for bug#4428980
AND     a.currency_code =  P_CURR_CODE
AND     B.REVERSAL_GL_DATE IS NULL
AND     b.current_record_flag = 'Y'
AND     a.org_id=NVL( P_ORGANIZATION_ID, a.org_id)
and     not exists  -- writing this query coz when a receipt is reversed , its write off details should not be shown
 (select   1
  from     ar_cash_receipt_history_all
  where    cash_receipt_id = b.cash_receipt_id
  and    status = lv_rev_status--rchandan for bug#4428980
)
;

Line: 533


  Select
         sum((b.amount)) sum_ext_amount,
         sum((b.amount) * NVL(a.exchange_rate,1))
  Into   v_cre_memo_amt,
         v_cre_memo_func_amt
From
        ra_customer_trx_all                A,
        ar_payment_schedules_all           C,
        ra_cust_trx_line_gl_dist_all       B
Where
        a.bill_to_customer_id =  P_CUSTOMER_ID
AND     a.customer_trx_id = c.customer_trx_id
AND     c.class In(lv_cm_class)--rchandan for bug#4428980
--AND     trunc(a.trx_date) <= trunc( p_start_date)
and     c.gl_date           <= trunc( p_start_date)
AND     a.invoice_currency_code =  P_CURR_CODE
AND     a.complete_flag = 'Y'
AND     b.customer_trx_id = a.customer_trx_id
AND     a.org_id = NVL( P_ORGANIZATION_ID, a.org_id) -- added by sriram
AND     b.account_class = lv_rec_account_class--rchandan for bug#4428980
AND     c.payment_schedule_id in
( select min(payment_schedule_id)
  from   ar_payment_schedules_all
 where   customer_trx_id = c.customer_trx_id
);

Line: 628


      SELECT
        ORGANIZATION_NAME
      FROM
        ORG_ORGANIZATION_DEFINITIONS
      WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 634


      SELECT
        LOCATION_ID,
        ADDRESS_LINE_1,
        ADDRESS_LINE_2,
        ADDRESS_LINE_3,
        COUNTRY
      FROM
        HR_ORGANIZATION_UNITS_V
      WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 644


      SELECT
        DESCRIPTION
      FROM
        HR_LOCATIONS
      WHERE LOCATION_ID = V_LOC_ID;