[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct trx_number
from fv_sf1081_temp
where alc_code = '1';
select distinct trx_number
from fv_sf1081_temp
where alc_code = '2';
select distinct customer_name
from fv_sf1081_temp
where alc_code = '3';
delete from fv_sf1081_temp where alc_code in ('1','2','3');
SELECT chart_of_accounts_id INTO flex_num
FROM gl_sets_of_books
WHERE set_of_books_id = v_set_of_books_id;
DELETE
FROM fv_sf1081_temp;
SELECT
RCT.TRX_NUMBER,
RCT.RECEIPT_METHOD_ID,
HZP.PARTY_NAME,
HZCA.CUST_ACCOUNT_ID,
HZCAS.CUST_ACCT_SITE_ID,
HZL.ADDRESS1,
HZL.ADDRESS2,
HZL.ADDRESS3,
HZL.CITY,
HZL.STATE,
HZL.POSTAL_CODE,
RCT.CUSTOMER_TRX_ID,
SUM(RLD.AMOUNT) AMOUNT,
RCT.BILL_TO_CUSTOMER_ID,
RCT.TRX_DATE,
RLD.CODE_COMBINATION_ID
FROM
RA_CUSTOMER_TRX RCT,
RA_CUSTOMER_TRX_LINES RTL,
RA_CUST_TRX_LINE_GL_DIST RLD,
HZ_CUST_SITE_USES HZCSU,
HZ_LOCATIONS HZL,
HZ_CUST_ACCT_SITES HZCAS,
HZ_CUST_ACCOUNTS HZCA,
HZ_PARTY_SITES HZPS,
HZ_PARTIES HZP
WHERE
RCT.CUSTOMER_TRX_ID = RTL.CUSTOMER_TRX_ID
AND RTL.CUSTOMER_TRX_LINE_ID = RLD.CUSTOMER_TRX_LINE_ID
AND RCT.COMPLETE_FLAG = 'Y'
AND RCT.PRINTING_OPTION = 'PRI'
AND RCT.SET_OF_BOOKS_ID = v_set_of_books_id
AND RCT.TRX_NUMBER BETWEEN NVL( parm_trans_num_low ,'0')
AND NVL( parm_trans_num_high ,'zzzzzzzzzzzzzzzzzzzz')
AND RCT.TRX_DATE BETWEEN NVL( parm_print_date_low , TO_DATE('1990/1/1', 'yyyy/mm/dd'))
AND NVL( parm_print_date_high , TRUNC(SYSDATE))
AND NVL(RCT.STATUS_TRX,'-1') LIKE DECODE( parm_open_invoices_only ,'Y','OP','N','%')
AND HZPS.PARTY_ID = HZCA.PARTY_ID
AND HZP.PARTY_ID = HZPS.PARTY_ID
AND RCT.BILL_TO_SITE_USE_ID = HZCSU.SITE_USE_ID
AND HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID
AND HZCAS.PARTY_SITE_ID = HZPS.PARTY_SITE_ID
AND HZPS.LOCATION_ID = HZL.LOCATION_ID
AND HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID
AND hzca.cust_account_id IN
((SELECT cust_account_id
FROM hz_cust_accounts hzca
WHERE NVL(customer_class_code,'XXX') LIKE
DECODE(parm_customer_class,null,
NVL(customer_class_code,'XXX'),parm_customer_class))
INTERSECT
(SELECT cust_account_id
FROM hz_cust_accounts hzca
WHERE cust_account_id LIKE NVL(parm_customer,'%')))
AND RCT.BILL_TO_CUSTOMER_ID IN
(SELECT HCP.CUST_ACCOUNT_ID
FROM HZ_CUSTOMER_PROFILES HCP
WHERE HCP.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND HCP.PROFILE_CLASS_ID = NVL(parm_cust_profile_class_id , HCP.PROFILE_CLASS_ID))
AND RCT.CUST_TRX_TYPE_ID IN
(SELECT CUST_TRX_TYPE_ID
FROM RA_CUST_TRX_TYPES RCTT
WHERE RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND ( RCTT.TYPE LIKE NVL(parm_transaction_class ,'%')
OR RCTT.CUST_TRX_TYPE_ID = NVL(parm_transaction_type, RCTT.CUST_TRX_TYPE_ID)))
AND ((RCT.BATCH_ID IN
(SELECT BATCH_ID
FROM RA_BATCHES RB
WHERE RB.BATCH_ID = RCT.BATCH_ID
AND RB.BATCH_ID = NVL( parm_batch ,RB.BATCH_ID)))
OR
(parm_batch is null and NVL(BATCH_ID,'99') LIKE DECODE( parm_print_choice ,'SEL','99','NEW','99')))
AND NVL(RCT.PRINTING_COUNT,'99') LIKE DECODE(parm_print_choice ,'NEW','99','%')
AND NVL(TO_CHAR(RCT.PRINTING_ORIGINAL_DATE,'DD-MM-YYYY'),'01-01-1999')
LIKE DECODE(parm_print_choice ,'NEW','01-01-1999','%')
AND NVL(TO_CHAR(RCT.PRINTING_LAST_PRINTED,'DD-MM-YYYY'),'01-01-1999')
LIKE DECODE(parm_print_choice ,'NEW','01-01-1999','%')
GROUP BY
RCT.TRX_NUMBER,
RCT.RECEIPT_METHOD_ID,
HZP.PARTY_NAME,
HZCA.CUST_ACCOUNT_ID,
HZCAS.CUST_ACCT_SITE_ID,
HZL.ADDRESS1,
HZL.ADDRESS2,
HZL.ADDRESS3,
HZL.CITY,
HZL.STATE,
HZL.POSTAL_CODE,
RCT.CUSTOMER_TRX_ID,
RCT.BILL_TO_CUSTOMER_ID,
RCT.TRX_DATE,
RLD.CODE_COMBINATION_ID
ORDER BY parm_order_by;
SELECT rct.trx_number,
rct.receipt_method_id,
hzp.party_name,
hzca.cust_account_id,
cba.agency_location_code,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.city,
hzl.state,
hzl.postal_code,
rct.customer_trx_id,
sum(rld.amount) Amount,
rct.bill_to_customer_id,
rct.trx_date,
rld.code_combination_id
FROM
hz_locations hzl,
hz_cust_acct_sites hzcas,
hz_party_sites hzps ,
HZ_CUST_SITE_USEs hzcsu,
hz_cust_accounts hzca,
hz_parties hzp,
ra_customer_trx rct,
ra_customer_trx_lines rtl,
ra_cust_trx_line_gl_dist rld,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
WHERE
hzp.party_id = hzca.party_id
AND hzca.cust_account_id = hzcas.cust_account_id
AND hzcas.party_site_id = hzps.party_site_id
AND hzps.location_id = hzl.location_id
AND hzcsu.Cust_Acct_site_ID = hzcas.CUST_ACCT_SITE_ID
AND hzps.party_id = hzp.party_id
AND rct.bill_to_site_use_id = hzcsu.site_use_id
AND rct.remit_bank_acct_use_id = cbau.bank_acct_use_id
AND cba.bank_account_id = cbau.bank_account_id
AND cba.account_owner_party_id = cbau.org_party_id
AND cba.account_classification = 'EXTERNAL'
AND cbau.org_id = v_org_id
AND cba.account_owner_org_id = cbau.org_id
AND cbau.primary_flag = 'Y'
AND cba.account_owner_party_id = hzp.party_id
AND cba.agency_location_code = parm_alc
AND rct.complete_flag = 'Y'
AND rct.printing_option = 'PRI'
AND rtl.customer_trx_line_id = rld.customer_trx_line_id
AND rct.customer_trx_id = rtl.customer_trx_id
AND rct.set_of_books_id = v_set_of_books_id
AND rct.bill_to_customer_id IN
(SELECT DISTINCT cust_account_id
FROM hz_customer_profiles
WHERE profile_class_id =
DECODE(parm_cust_profile_class_id,null,profile_class_id,
parm_cust_profile_class_id))
AND rct.cust_trx_type_id IN
((SELECT cust_trx_type_id
FROM ra_cust_trx_types
WHERE type LIKE NVL(parm_transaction_class,'%'))
INTERSECT
(SELECT cust_trx_type_id
FROM ra_cust_trx_types
WHERE cust_trx_type_id = DECODE(parm_transaction_type,null,
cust_trx_type_id,parm_transaction_type)))
AND (rct.trx_number BETWEEN NVL(parm_trans_num_low,'0')
AND NVL(parm_trans_num_high,'zzzzzzzzzzzzzzzzzzzz'))
AND rct.trx_date BETWEEN DECODE(parm_print_date_low,null,TO_DATE('1990/1/1', 'yyyy/mm/dd'), parm_print_date_low)
AND DECODE(parm_print_date_high,null,trunc(sysdate),parm_print_date_high )
AND hzca.cust_account_id IN
((SELECT cust_account_id
FROM hz_cust_accounts hzca
WHERE NVL(customer_class_code,'XXX') LIKE DECODE(parm_customer_class,null,
NVL(customer_class_code,'XXX'),parm_customer_class))
INTERSECT
(SELECT cust_account_id
FROM hz_cust_accounts hzca
WHERE cust_account_id LIKE NVL(parm_customer,'%')))
AND rct.customer_trx_id IN
(SELECT customer_trx_id
FROM ra_customer_trx
WHERE NVL(status_trx,'-1') LIKE decode(parm_open_invoices_only,'Y','OP','N','%'))
AND ((rct.customer_trx_id IN
(SELECT customer_trx_id
FROM ra_customer_trx
WHERE batch_id IN
(SELECT batch_id
FROM ra_batches
WHERE batch_id = DECODE(parm_batch,null,BATCH_ID,parm_batch))))
OR (parm_batch is null and (NVL(BATCH_ID,'99') LIKE DECODE(parm_print_choice,'SEL','99','NEW','99'))))
AND rct.customer_trx_id IN
(SELECT rct.customer_trx_id
FROM ra_customer_trx rct
WHERE (NVL(rct.printing_count,'99') LIKE
DECODE(parm_print_choice,'NEW','99','%'))
AND (NVL(TO_CHAR(rct.printing_original_date,'DD-MM-YYYY'),'01-01-1999') LIKE
DECODE(parm_print_choice,'NEW','01-01-1999','%'))
AND (NVL(TO_CHAR(rct.printing_last_printed,'DD-MM-YYYY'),'01-01-1999') LIKE
DECODE(parm_print_choice,'NEW','01-01-1999','%')))
GROUP BY rct.trx_number,cba.agency_location_code,rct.receipt_method_id,
hzp.party_name, hzca.cust_account_id,
hzl.address1,hzl.address2,hzl.address3,hzl.city,hzl.state,hzl.postal_code,
rct.customer_trx_id,rct.bill_to_customer_id, rct.trx_date, rld.code_combination_id
ORDER BY parm_order_by;
select alc_code
into v_default_alc
from fv_operating_units_all
where default_alc = 'Y'
and nvl(org_id,-99) = nvl(v_org_id,-99);
SELECT eb.agency_location_code
INTO vc_cust_alc
FROM hz_cust_acct_sites_all hzcas,
hz_cust_site_uses_all hzcsu,
iby_external_payers_all payer,
iby_pmt_instr_uses_all iby_ins,
iby_ext_bank_accounts_v eb
WHERE hzcas.cust_account_id = vc_customer_id
AND hzcas.cust_acct_site_id = vc_address_id
AND hzcsu.cust_acct_site_id=hzcas.cust_acct_site_id
AND hzcsu.site_use_code = 'BILL_TO'
AND hzcsu.site_use_id = payer.acct_site_use_id
AND payer.ext_payer_id= iby_ins.ext_pmt_party_id
AND iby_ins.instrument_type = 'BANKACCOUNT'
AND iby_ins.instrument_id = eb.ext_bank_account_id
--Bug8654464
--AND iby_ins.start_date < vc_trx_date
AND Decode(iby_ins.start_date,NULL,(vc_trx_date-1),iby_ins.start_date) < vc_trx_date
AND (Decode(iby_ins.end_date,NULL,Sysdate,iby_ins.end_date))> vc_trx_date
and iby_ins.payment_function = 'CUSTOMER_PAYMENT' ;
select cba.agency_location_code
into v_alc_code
from ar_receipt_method_accounts arma,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
where arma.primary_flag = 'Y'
and arma.receipt_method_id = vc_receipt_method_id
and cbau.bank_acct_use_id = arma.remit_bank_acct_use_id
and cba.bank_account_id = cbau.bank_account_id
and cbau.org_id = v_org_id;
SELECT fts.treasury_symbol
INTO v_treasury_symbol
FROM fv_fund_parameters ffp,
fv_treasury_symbols fts,
gl_code_combinations glc
WHERE decode(v_bal_seg_name,'SEGMENT1', glc.segment1,
'SEGMENT2', glc.segment2,
'SEGMENT3', glc.segment3,
'SEGMENT4', glc.segment4,
'SEGMENT5', glc.segment5,
'SEGMENT6', glc.segment6,
'SEGMENT7', glc.segment7,
'SEGMENT8', glc.segment8,
'SEGMENT9', glc.segment9,
'SEGMENT10', glc.segment10,
'SEGMENT11', glc.segment11,
'SEGMENT12', glc.segment12,
'SEGMENT13', glc.segment13,
'SEGMENT14', glc.segment14,
'SEGMENT15', glc.segment15,
'SEGMENT16', glc.segment16,
'SEGMENT17', glc.segment17,
'SEGMENT18', glc.segment18,
'SEGMENT19', glc.segment19,
'SEGMENT20', glc.segment20,
'SEGMENT21', glc.segment21,
'SEGMENT22', glc.segment22,
'SEGMENT23', glc.segment23,
'SEGMENT24', glc.segment24,
'SEGMENT25', glc.segment25,
'SEGMENT26', glc.segment26,
'SEGMENT27', glc.segment27,
'SEGMENT28', glc.segment28,
'SEGMENT29', glc.segment29,
'SEGMENT30', glc.segment30) = ffp.fund_value
AND glc.code_combination_id = vc_code_combination_id
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.set_of_books_id = v_set_of_books_id;
INSERT INTO fv_sf1081_temp
(customer_trx_id,
trx_number,
customer_name,
cust_address1,
cust_address2,
cust_address3,
cust_city,
cust_state,
cust_postal_code,
tax_reference,
treasury_symbol,
amount,
bill_to_customer_id,
trx_date,
alc_code)
VALUES
(vc_customer_trx_id,
vc_trx_number,
vc_customer_name,
vc_address1,
vc_address2,
vc_address3,
vc_city,
vc_state,
vc_postal_code,
vc_cust_alc,
v_treasury_symbol,
vc_amount,
vc_bill_to_customer_id,
vc_trx_date,
decode(v_warning_num,'1','1','2','2','3','3',v_alc_code));
g_error_message := 'INSERT_info: /'||SQLERRM;