[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
TO_CHAR(SYSDATE
,'DD-MON-YYYY HH24:MI:SS')
INTO CP_SYSDATE
FROM
DUAL;
SELECT
DSC.NAME,
DS.DOC_SEQUENCE_ID,
DSU.NEXTVAL
FROM
FND_DOC_SEQUENCE_CATEGORIES DSC,
FND_DOCUMENT_SEQUENCES DS,
FND_DOC_SEQUENCE_ASSIGNMENTS DSA,
FND_DOC_SEQUENCE_USERS DSU
WHERE DSA.CATEGORY_CODE = DSC.CODE
AND DSA.APPLICATION_ID = DSC.APPLICATION_ID
AND DS.DOC_SEQUENCE_ID = DSA.DOC_SEQUENCE_ID
AND DSU.DOC_SEQUENCE_ID = DSA.DOC_SEQUENCE_ID
AND DSA.DOC_SEQUENCE_ASSIGNMENT_ID = P_DOC_SEQ_ASSIGN_ID
AND DSA.SET_OF_BOOKS_ID = P_SOB_ID
AND DSA.END_DATE is NULL;
SELECT
DISTINCT
MIN(HP.PARTY_NAME),
MAX(HP.PARTY_NAME)
FROM
AR_CASH_RECEIPTS ACR,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA
WHERE ( ACR.GLOBAL_ATTRIBUTE1 is NULL
AND ACR.GLOBAL_ATTRIBUTE2 is NULL )
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = ACR.PAY_FROM_CUSTOMER;
SELECT
DISTINCT
MIN(RECEIPT_DATE),
MAX(RECEIPT_DATE)
FROM
AR_CASH_RECEIPTS CR
WHERE ( CR.GLOBAL_ATTRIBUTE1 is NULL
AND CR.GLOBAL_ATTRIBUTE2 is NULL );
SELECT
SUBSTR(HR.LOCATION_CODE
,1
,20),
SUBSTR(HR.ADDRESS_LINE_1
,1
,60),
SUBSTR(HR.ADDRESS_LINE_2
,1
,60),
SUBSTR(HR.ADDRESS_LINE_3
,1
,60),
HR.POSTAL_CODE || ' ' || HR.TOWN_OR_CITY,
FT2.TERRITORY_SHORT_NAME,
HR.TELEPHONE_NUMBER_1,
HR.TELEPHONE_NUMBER_2,
HR.TELEPHONE_NUMBER_3,
XFI.REGISTRATION_NUMBER
FROM
HR_LOCATIONS HR,
XLE_FIRSTPARTY_INFORMATION_V XFI,
FND_TERRITORIES_VL FT2
WHERE XFI.LEGAL_ENTITY_ID = P_ORG_ID
AND XFI.LOCATION_ID = HR.LOCATION_ID
AND FT2.TERRITORY_CODE = HR.COUNTRY
AND HR.OFFICE_SITE_FLAG = 'Y';
SELECT
DISTINCT
MIN(RECEIPT_DATE),
MAX(RECEIPT_DATE)
INTO P_RECEIPT_FROM,P_RECEIPT_TO
FROM
AR_CASH_RECEIPTS CR
WHERE TO_NUMBER(CR.GLOBAL_ATTRIBUTE1) >= P_RA_FROM
AND TO_NUMBER(CR.GLOBAL_ATTRIBUTE1) <= P_RA_TO;
SELECT
NAME,
PRECISION
INTO L_CURRENCY_NAME,L_PRECISION
FROM
FND_CURRENCIES_VL
WHERE CURRENCY_CODE = C_CURR_TOTAL_CURRENCY;
SELECT
DISTINCT
HCA.CUST_ACCOUNT_ID,
CR.CURRENCY_CODE,
HCSU.SITE_USE_ID
FROM
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_SITE_USES HCSU,
HZ_LOCATIONS HL,
HZ_CUST_ACCT_SITES HCAS,
AR_CASH_RECEIPTS CR,
AR_CASH_RECEIPT_HISTORY CRH,
HZ_PARTY_SITES HPS
WHERE HP.PARTY_ID = HCA.PARTY_ID
AND ( HP.PARTY_NAME >= C_FROM
AND HP.PARTY_NAME <= C_TO )
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND UPPER(HCSU.SITE_USE_CODE) = 'BILL_TO'
AND HCSU.GLOBAL_ATTRIBUTE1 = 'Y'
AND ( CR.GLOBAL_ATTRIBUTE1 is NULL
AND CR.GLOBAL_ATTRIBUTE2 is NULL )
AND CR.RECEIPT_DATE >= P_RECEIPT_FROM
AND CR.RECEIPT_DATE <= P_RECEIPT_TO
AND CRH.STATUS = 'CLEARED'
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID;
CURSOR UPDATE_AR_CASH_RECEIPTS(C_CUST IN NUMBER,C_CURRENCY IN VARCHAR2,C_SITE_USE_ID IN NUMBER) IS
SELECT
ROWID
FROM
AR_CASH_RECEIPTS CR
WHERE CR.PAY_FROM_CUSTOMER = C_CUST
AND CR.CURRENCY_CODE = C_CURRENCY
AND CR.CUSTOMER_SITE_USE_ID = C_SITE_USE_ID
AND CR.GLOBAL_ATTRIBUTE1 is NULL
AND CR.GLOBAL_ATTRIBUTE2 is NULL
AND CR.RECEIPT_DATE >= P_RECEIPT_FROM
AND CR.RECEIPT_DATE <= P_RECEIPT_TO
AND exists (
SELECT
CRH.CASH_RECEIPT_ID
FROM
AR_CASH_RECEIPT_HISTORY CRH
WHERE CRH.STATUS = 'CLEARED'
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID );
FOR receipt_row IN UPDATE_AR_CASH_RECEIPTS(C_CUST,C_CURRENCY,C_SITE_USE_ID) LOOP
UPDATE
AR_CASH_RECEIPTS cr
SET
CR.GLOBAL_ATTRIBUTE1 = C_RECEIPT
,CR.GLOBAL_ATTRIBUTE2 = TO_CHAR(SYSDATE
,'YYYY/MM/DD HH24:MI:SS')
,CR.GLOBAL_ATTRIBUTE3 = CP_CATEGORY
WHERE CR.ROWID = RECEIPT_ROW.ROWID;
INSERT INTO AR_DOC_SEQUENCE_AUDIT
(DOC_SEQUENCE_ID
,DOC_SEQUENCE_ASSIGNMENT_ID
,DOC_SEQUENCE_VALUE
,CREATION_DATE
,CREATED_BY)
VALUES (CP_DOC_SEQ_ID
,P_DOC_SEQ_ASSIGN_ID
,CP_DOC_VALUE
,SYSDATE
,0);
SELECT
INITCAP(LOWER('e' || ' ' || L_WORD_AMOUNT2 || ' ' || DECODE(L_DECIMAL_PART
,1
,P_SUB_UNIT_SINGULAR
,P_SUB_UNIT_PLURAL)))
INTO L_WORD_AMOUNT2
FROM
DUAL;
SELECT
INITCAP(LOWER(L_WORD_AMOUNT || ' ' || DECODE(TRUNC(P_AMOUNT)
,1
,P_UNIT_SINGULAR
,P_UNIT_PLURAL)))
INTO L_CURRENCY_WORD
FROM
DUAL;
SELECT
INITCAP(LOWER(L_WORD_AMOUNT || ' ' || DECODE(TRUNC(P_AMOUNT)
,1
,P_UNIT_SINGULAR
,P_UNIT_PLURAL) || ' ' || L_WORD_AMOUNT2))
INTO L_CURRENCY_WORD
FROM
DUAL;
SELECT
PRECISION
INTO L_PRECISION
FROM
FND_CURRENCIES_VL
WHERE CURRENCY_CODE = CUR_CODE;