[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CP.USER_CONCURRENT_PROGRAM_NAME
INTO L_REPORT_NAME
FROM
FND_CONCURRENT_PROGRAMS_VL CP,
FND_CONCURRENT_REQUESTS CR
WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
SELECT
RP.CUSTOMER_TRX_ID,
RP.TRX_DATE,
RP.TERM_DUE_DATE,
RP.GD_GL_DATE,
RP.BATCH_SOURCE_ID,
RP.CUST_TRX_TYPE_ID,
RP.ACTIVITY_FLAG,
RP.POSTED_FLAG,
RP.CTT_CLASS,
RP.COMPLETE_FLAG,
RP.GLOBAL_ATTRIBUTE_CATEGORY,
RP.GLOBAL_ATTRIBUTE20,
RP.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
FROM
RA_CUSTOMER_TRX_PARTIAL_V RP
WHERE RP.BATCH_SOURCE_ID = P_IN_BATCH_SOURCE_ID
AND RP.CUST_TRX_TYPE_ID = NVL(P_IN_CUST_TRX_TYPE_ID
,RP.CUST_TRX_TYPE_ID)
AND ( ( P_IN_NUMBER_TYPE = 'DOC_NUM'
AND RP.DOC_SEQUENCE_VALUE between P_IN_NUMBER_LOW
AND P_IN_NUMBER_HIGH )
OR ( P_IN_NUMBER_TYPE = 'TRX_NUM'
AND RP.TRX_NUMBER between P_IN_NUMBER_LOW
AND P_IN_NUMBER_HIGH ) )
AND RP.GLOBAL_ATTRIBUTE_CATEGORY = DECODE(X_COUNTRY_CODE
,'CL'
,'JL.CL.ARXTWMAI.TGW_HEADER'
,'AR'
,'JL.AR.ARXTWMAI.TGW_HEADER'
,'CO'
,'JL.CO.ARXTWMAI.TGW_HEADER'
,NULL)
UNION
SELECT
RP.CUSTOMER_TRX_ID,
RP.TRX_DATE,
RP.TERM_DUE_DATE,
RP.GD_GL_DATE,
RP.BATCH_SOURCE_ID,
RP.CUST_TRX_TYPE_ID,
RP.ACTIVITY_FLAG,
RP.POSTED_FLAG,
RP.CTT_CLASS,
RP.COMPLETE_FLAG,
RP.GLOBAL_ATTRIBUTE_CATEGORY,
RP.GLOBAL_ATTRIBUTE20,
RP.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
FROM
RA_CUSTOMER_TRX_PARTIAL_V RP
WHERE RP.BATCH_SOURCE_ID = P_IN_BATCH_SOURCE_ID
AND RP.CUST_TRX_TYPE_ID = NVL(P_IN_CUST_TRX_TYPE_ID
,RP.CUST_TRX_TYPE_ID)
AND ( ( P_IN_NUMBER_TYPE = 'DOC_NUM'
AND RP.DOC_SEQUENCE_VALUE between P_IN_NUMBER_LOW
AND P_IN_NUMBER_HIGH )
OR ( P_IN_NUMBER_TYPE = 'TRX_NUM'
AND RP.TRX_NUMBER between P_IN_NUMBER_LOW
AND P_IN_NUMBER_HIGH ) )
AND ( RP.GLOBAL_ATTRIBUTE_CATEGORY IS NULL
OR RP.GLOBAL_ATTRIBUTE_CATEGORY = DECODE(X_COUNTRY_CODE
,'CL'
,'JL.CL.ARXTWMAI.TGW_HEADER'
,'AR'
,'JL.AR.ARXTWMAI.TGW_HEADER'
,'CO'
,'JL.CO.ARXTWMAI.TGW_HEADER'
,NULL) )
AND RP.GLOBAL_ATTRIBUTE20 IS NULL
ORDER BY
13;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
IF (INSERT_RECUR_INTERIM(L_REQUEST_ID
,L_CUSTOMER_TRX_ID
,L_TRX_DATE
,L_TERM_DUE_DATE
,L_GL_DATE
,L_BATCH_SOURCE_ID
,L_TRX_NUMBER_OUT) <> TRUE) THEN
/*SRW.MESSAGE('999'
,'insert_recur_interim <- ' || L_LOC)*/NULL;
IF (UPDATE_ORIGINAL_TRX(P_COUNTRY_CODE
,L_CUSTOMER_TRX_ID
,L_TRX_NUMBER_OUT
,'E') <> TRUE) THEN
/*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
IF (UPDATE_ORIGINAL_TRX(P_COUNTRY_CODE
,L_CUSTOMER_TRX_ID
,L_TRX_NUMBER_OUT
,'P') <> TRUE) THEN
/*SRW.MESSAGE('999'
,'update_original_trx <- ' || L_LOC)*/NULL;
IF (UPDATE_ORIGINAL_TRX(P_COUNTRY_CODE
,L_CUSTOMER_TRX_ID
,L_TRX_NUMBER_OUT
,'E') <> TRUE) THEN
/*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
SELECT
'Y'
INTO L_DUMMY
FROM
RA_BATCH_SOURCES
WHERE BATCH_SOURCE_ID = P_BATCH_SOURCE_ID
AND AUTO_TRX_NUMBERING_FLAG = 'Y';
FUNCTION UPDATE_ORIGINAL_TRX(P_COUNTRY_CODE IN VARCHAR2
,P_CUSTOMER_TRX_ID IN NUMBER
,P_NEW_TRX_NUMBER IN VARCHAR2
,P_COPY_TRX_STATUS IN VARCHAR2) RETURN BOOLEAN IS
CURSOR C_ORIG_TRX_FOR_UPDATE IS
SELECT
CT.CUSTOMER_TRX_ID,
CT.CUST_TRX_TYPE_ID
FROM
RA_CUSTOMER_TRX CT
WHERE CT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
CURSOR C_ORIG_TRX_GL_DIST_FOR_UPDATE IS
SELECT
CTGLD.CUSTOMER_TRX_ID
FROM
RA_CUST_TRX_LINE_GL_DIST CTGLD
WHERE CTGLD.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
FOR UPDATE OF GL_DATE NOWAIT;
SELECT
CUST_TRX_TYPE_ID
INTO L_VOID_TRX_TYPE_ID
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.CL.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
SELECT
CUST_TRX_TYPE_ID
INTO L_VOID_TRX_TYPE_ID
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.AR.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
SELECT
CUST_TRX_TYPE_ID
INTO L_VOID_TRX_TYPE_ID
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.CO.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
OPEN C_ORIG_TRX_FOR_UPDATE;
FETCH C_ORIG_TRX_FOR_UPDATE
INTO L_CUSTOMER_TRX_ID,L_ORIG_CUST_TRX_TYPE_ID;
EXIT WHEN C_ORIG_TRX_FOR_UPDATE%NOTFOUND;
L_TRX_REC.LAST_UPDATED_BY := L_USER_ID;
L_TRX_REC.LAST_UPDATE_DATE := SYSDATE;
L_TRX_REC.LAST_UPDATE_LOGIN := NVL(L_CONC_LOGIN_ID
,L_LOGIN_ID);
L_TRX_REC.PROGRAM_UPDATE_DATE := SYSDATE;
ARP_CT_PKG.UPDATE_P(L_TRX_REC
,L_CUSTOMER_TRX_ID);
OPEN C_ORIG_TRX_GL_DIST_FOR_UPDATE;
FETCH C_ORIG_TRX_GL_DIST_FOR_UPDATE
INTO L_GL_DIST_CUST_TRX_ID;
EXIT WHEN C_ORIG_TRX_GL_DIST_FOR_UPDATE%NOTFOUND;
UPDATE
RA_CUST_TRX_LINE_GL_DIST
SET
GL_DATE = NULL
WHERE CURRENT OF C_ORIG_TRX_GL_DIST_FOR_UPDATE;
,'Updated GL Distributions:' || ' l_gl_dist_cust_trx_id = ' || TO_CHAR(L_GL_DIST_CUST_TRX_ID))*/NULL;
CLOSE C_ORIG_TRX_GL_DIST_FOR_UPDATE;
L_TRX_REC.LAST_UPDATED_BY := L_USER_ID;
L_TRX_REC.LAST_UPDATE_DATE := SYSDATE;
L_TRX_REC.LAST_UPDATE_LOGIN := NVL(L_CONC_LOGIN_ID
,L_LOGIN_ID);
L_TRX_REC.PROGRAM_UPDATE_DATE := SYSDATE;
ARP_CT_PKG.UPDATE_P(L_TRX_REC
,L_CUSTOMER_TRX_ID);
,'The original invoice is updated with copy status')*/NULL;
CLOSE C_ORIG_TRX_FOR_UPDATE;
,'Invalid Country Code: update_original_trx <- beforerep')*/NULL;
,'update_original_trx <- beforerep')*/NULL;
END UPDATE_ORIGINAL_TRX;
SELECT
CUST_TRX_TYPE_ID,
ACCOUNTING_AFFECT_FLAG,
POST_TO_GL
INTO L_CUST_TRX_TYPE_ID,L_OPEN_AR,L_POST_TO_GL
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.CL.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
SELECT
CUST_TRX_TYPE_ID,
ACCOUNTING_AFFECT_FLAG,
POST_TO_GL
INTO L_CUST_TRX_TYPE_ID,L_OPEN_AR,L_POST_TO_GL
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.AR.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
SELECT
CUST_TRX_TYPE_ID,
ACCOUNTING_AFFECT_FLAG,
POST_TO_GL
INTO L_CUST_TRX_TYPE_ID,L_OPEN_AR,L_POST_TO_GL
FROM
RA_CUST_TRX_TYPES
WHERE GLOBAL_ATTRIBUTE_CATEGORY = 'JL.CO.RAXSUCTT.CUST_TRX_TYPES'
AND GLOBAL_ATTRIBUTE6 = 'Y';
FUNCTION INSERT_RECUR_INTERIM(P_REQUEST_ID IN NUMBER
,P_CUSTOMER_TRX_ID IN NUMBER
,P_TRX_DATE IN DATE
,P_TERM_DUE_DATE IN DATE
,P_GL_DATE IN DATE
,P_BATCH_SOURCE_ID IN NUMBER
,P_NEW_TRX_NUMBER OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
L_LOC VARCHAR2(30) := 'INSERT_RECUR_INTERIM';
JL_AR_RECUR_PKG.INSERT_INTERIM(P_CUSTOMER_TRX_ID
,P_TRX_DATE
,P_TERM_DUE_DATE
,P_GL_DATE
,NULL
,P_REQUEST_ID
,NULL
,NULL
,P_BATCH_SOURCE_ID
,P_NEW_TRX_NUMBER);
END INSERT_RECUR_INTERIM;
SELECT
MAX(DOC_SEQUENCE_VALUE)
INTO L_MAX_DOC_SEQ_NUM
FROM
RA_CUSTOMER_TRX_ALL
WHERE DOC_SEQUENCE_VALUE <= TO_NUMBER(P_NUMBER_HIGH);
SELECT
MAX(TRX_NUMBER)
INTO L_MAX_TRX_NUM
FROM
RA_CUSTOMER_TRX_ALL
WHERE TRX_NUMBER <= P_NUMBER_HIGH;
SELECT
NAME
INTO L_BATCH_SOURCE_NAME
FROM
RA_BATCH_SOURCES
WHERE BATCH_SOURCE_ID = P_IN_BATCH_SOURCE_ID;
SELECT
MEANING
INTO L_NUMBER_TYPE
FROM
FND_LOOKUPS
WHERE LOOKUP_TYPE = 'JLZZ_CVI_NUMBER_TYPE'
AND LOOKUP_CODE = P_IN_NUMBER_TYPE;