[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 TRUNC(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 B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
AND B.LATEST_REC_FLAG = 'Y'
AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
AND A.ORG_ID = P_ORG_ID
AND C.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
SELECT
SUM((B.AMOUNT)) SUM_ADJ_AMOUNT,
SUM((B.AMOUNT) * NVL(C.EXCHANGE_RATE
,1))
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 E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
AND TRUNC(B.GL_DATE) < TRUNC(P_START_DATE)
AND C.INVOICE_CURRENCY_CODE = CURR_CODE
AND B.STATUS = 'A'
AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
AND C.ORG_ID = P_ORG_ID
AND D.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID );
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.CURRENCY_CODE = CURR_CODE
AND A.ORG_ID = P_ORG_ID
AND EXISTS (
SELECT
1
FROM
AR_CASH_RECEIPT_HISTORY_ALL
WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
AND ORG_ID = P_ORG_ID
AND TRUNC(GL_DATE) < TRUNC(P_START_DATE) );
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.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
AND A.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND TRUNC(B.GL_DATE) < TRUNC(P_START_DATE)
AND A.REVERSAL_DATE is not null
AND A.CURRENCY_CODE = CURR_CODE
AND B.STATUS = LV_REV_STATUS
AND A.ORG_ID = P_ORG_ID;
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 = P_ORG_ID
AND D.APPLICATION_TYPE = CP_APP_TYPE
AND D.DISPLAY = 'Y';
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 = P_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);
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 = P_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);
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 = P_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 );
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 TRUNC(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 B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
AND B.LATEST_REC_FLAG = 'Y'
AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.CLASS In ( LV_CM_CLASS )
AND A.ORG_ID = P_ORG_ID
AND C.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
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_ID2
AND TRUNC(C.GL_DATE) <= TRUNC(P_END_DATE)
AND A.INVOICE_CURRENCY_CODE = CURR_CODE1
AND A.COMPLETE_FLAG = 'Y'
AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
AND B.LATEST_REC_FLAG = 'Y'
AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.CLASS In ( LV_INV_CLASS , LV_DM_CLASS , LV_DEP_CLASS )
AND A.ORG_ID = P_ORG_ID
AND C.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
SELECT
SUM((B.AMOUNT)) SUM_ADJ_AMOUNT,
SUM((B.AMOUNT) * NVL(C.EXCHANGE_RATE
,1))
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 E.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
AND C.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
AND TRUNC(B.GL_DATE) <= TRUNC(P_END_DATE)
AND C.INVOICE_CURRENCY_CODE = CURR_CODE1
AND B.STATUS = 'A'
AND B.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
AND C.ORG_ID = P_ORG_ID
AND D.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID );
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_ID2
AND A.CURRENCY_CODE = CURR_CODE1
AND A.ORG_ID = P_ORG_ID
AND EXISTS (
SELECT
1
FROM
AR_CASH_RECEIPT_HISTORY_ALL
WHERE CASH_RECEIPT_ID = A.CASH_RECEIPT_ID
AND ORG_ID = P_ORG_ID
AND TRUNC(GL_DATE) <= TRUNC(P_END_DATE) );
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.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
AND A.PAY_FROM_CUSTOMER = CUSTOMER_ID2
AND TRUNC(B.GL_DATE) <= TRUNC(P_END_DATE)
AND A.REVERSAL_DATE is not null
AND A.CURRENCY_CODE = CURR_CODE1
AND B.STATUS = LV_REV_STATUS
AND A.ORG_ID = P_ORG_ID;
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_ID2
AND B.COMPLETE_FLAG = 'Y'
AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE)
AND D.APPLIED_CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
AND B.ORG_ID = P_ORG_ID
AND D.EARNED_DISCOUNT_TAKEN is not null
AND D.EARNED_DISCOUNT_TAKEN <> 0
AND D.APPLICATION_TYPE = CP_APP_TYPE
AND D.DISPLAY = 'Y';
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 = P_ORG_ID
AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE )
AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE);
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 = P_ORG_ID
AND B.INVOICE_CURRENCY_CODE = CURR_CODE1
AND B.BILL_TO_CUSTOMER_ID = CUSTOMER_ID2
AND TRUNC(D.GL_DATE) <= TRUNC(P_END_DATE)
AND E.SOURCE_TYPE IN ( LV_LOSS_SOURCE_TYPE , LV_GAIN_SOURCE_TYPE );
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_ID2
AND TRUNC(B.GL_DATE) <= TRUNC(P_END_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_CODE1
AND B.REVERSAL_GL_DATE IS NULL
AND B.CURRENT_RECORD_FLAG = 'Y'
AND A.ORG_ID = P_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 );
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_ID2
AND TRUNC(C.GL_DATE) <= TRUNC(P_END_DATE)
AND A.INVOICE_CURRENCY_CODE = CURR_CODE1
AND A.COMPLETE_FLAG = 'Y'
AND B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
AND B.ACCOUNT_CLASS = LV_REC_ACCOUNT_CLASS
AND B.LATEST_REC_FLAG = 'Y'
AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.CLASS In ( LV_CM_CLASS )
AND A.ORG_ID = P_ORG_ID
AND C.PAYMENT_SCHEDULE_ID IN (
SELECT
MIN(PAYMENT_SCHEDULE_ID)
FROM
AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID );
SELECT
INTERFACE_HEADER_ATTRIBUTE1
FROM
RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID_1
AND NVL(CREATED_FROM
,'###') = 'RAXTRX';
SELECT
DISTINCT
CPC.NAME PROFILE_CLASS_NAME
FROM
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_PARTIES CUST_PARTY,
HZ_CUST_PROFILE_CLASSES CPC,
HZ_CUSTOMER_PROFILES CP
WHERE CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID
AND CP.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = CPC.PROFILE_CLASS_ID (+)
AND CUST_ACCT.CUST_ACCOUNT_ID = CUSTOMER_ID;
SELECT
ORGANIZATION_NAME
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_ORG_ID;
SELECT
LOCATION_ID,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
COUNTRY
FROM
HR_ORGANIZATION_UNITS_V
WHERE ORGANIZATION_ID = P_ORG_ID;
SELECT
DESCRIPTION
FROM
HR_LOCATIONS
WHERE LOCATION_ID = V_LOC_ID;
SELECT
DISTINCT
APPLICATION_COLUMN_NAME
FROM
FND_SEGMENT_ATTRIBUTE_VALUES
WHERE APPLICATION_ID = 101
AND ID_FLEX_CODE = CP_ID_FLEX_CODE
AND ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID
AND SEGMENT_ATTRIBUTE_TYPE = CP_SEG_ATT_TYPE
AND ATTRIBUTE_VALUE = 'Y';
SELECT
A.FLEX_VALUE_SET_ID
FROM
FND_ID_FLEX_SEGMENTS A
WHERE A.APPLICATION_COLUMN_NAME = V_COLUMN_NAME
AND A.APPLICATION_ID = 101
AND A.ID_FLEX_CODE = CP_ID_FLEX_CODE
AND A.ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID;
SELECT
SUBSTR(DESCRIPTION
,1
,15)
FROM
FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = V_FLEX_ID
AND FLEX_VALUE = V_COLUMN_VALUE;
'select ' || V_COLUMN_NAME || ' from gl_code_combinations
where chart_of_accounts_id = :P_CHART_OF_ACCOUNTS_ID AND code_combination_id = :account_id'
INTO p_column_value
USING P_CHART_OF_ACCOUNTS_ID,account_id ;
SELECT
A.EXCISE_INVOICE_NO
INTO V_EXCISE_INV_NO
FROM
JAI_AR_TRX_LINES A
WHERE CUSTOMER_TRX_ID = CUSTOMER_TRX_ID_1
AND A.EXCISE_INVOICE_NO IS NOT NULL
AND ROWNUM = 1;
SELECT
CONCURRENT_PROGRAM_ID,
NVL(ENABLE_TRACE
,'N')
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_REQUEST_ID;
SELECT
A.SID,
A.SERIAL#,
B.SPID
FROM
V$SESSION A,
V$PROCESS B
WHERE AUDSID = USERENV('SESSIONID')
AND A.PADDR = B.ADDR;
SELECT
NAME
FROM
V$DATABASE;