[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE
FROM FV_SF1080_HEADER_TEMP;
DELETE
FROM FV_SF1080_DETAIL_TEMP;
SELECT
rct.customer_trx_id customer_trx_id,
rct.trx_number trx_number,
rct.bill_to_customer_id bill_to_customer_id,
rct.remit_to_address_id remit_to_address_id,
hzp.party_name customer_name,
hzl.address1 cust_address1,
hzl.address2 cust_address2,
hzl.address3 cust_address3,
hzl.address4 cust_address4,
hzl.city cust_city,
hzl.state cust_state,
hzl.postal_code cust_postal_code
FROM RA_CUSTOMER_TRX rct,
hz_locations hzl, hz_cust_acct_sites hzcas,
hz_party_sites hzps , HZ_CUST_SITE_USEs hzcsu,hz_cust_accounts hzca, hz_parties hzp
WHERE rct.set_of_books_id = parm_set_of_books_id
AND rct.bill_to_customer_id = hzca.cust_account_id
AND hzca.cust_Account_id = hzcas.cust_account_id
AND hzp.party_id = hzca.party_id
AND hzca.cust_account_id = hzcas.cust_account_id
AND hzca.party_id = hzp.party_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 rct.complete_flag = 'Y'
AND rct.printing_option = 'PRI'
AND hzcsu.site_use_id = rct.bill_to_site_use_id
AND rct.bill_to_customer_id IN
(SELECT 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 (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','%')))
ORDER BY decode(parm_order_by,'TRX_NUMBER',rct.trx_number,
'POSTAL_CODE',hzl.postal_code,
'CUSTOMER_NAME',hzp.party_name,rct.trx_number);
SELECT ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,POSTAL_CODE
INTO REMIT_ADDRESS1,REMIT_ADDRESS2,REMIT_ADDRESS3,REMIT_ADDRESS4,
REMIT_CITY,REMIT_STATE,REMIT_POSTAL_CODE
FROM hz_locations hzl, hz_cust_acct_sites hzcas, hz_party_sites hzps
WHERE hzcas.cust_Acct_site_id =
TS_REPORT_HEADER_ENTRY.REMIT_TO_ADDRESS_ID
AND hzcas.party_site_id = hzps.party_site_id
AND hzps.location_id = hzl.location_id;
INSERT INTO FV_SF1080_HEADER_TEMP
( CUSTOMER_TRX_ID,
TRX_NUMBER,
CUSTOMER_NAME,
CUST_ADDRESS1,
REMIT_TO_ADDRESS_ADDRESS1,
CUST_ADDRESS2,
CUST_ADDRESS3,
CUST_ADDRESS4,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
REMIT_TO_ADDRESS_ADDRESS2,
REMIT_TO_ADDRESS_ADDRESS3,
REMIT_TO_ADDRESS_ADDRESS4,
REMIT_TO_ADDRESS_CITY,
REMIT_TO_ADDRESS_STATE,
REMIT_TO_ADDRESS_POSTAL_CODE,
ACCT_OFC_CHRG)
VALUES
(TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID,
TS_REPORT_HEADER_ENTRY.TRX_NUMBER,
TS_REPORT_HEADER_ENTRY.CUSTOMER_NAME,
TS_REPORT_HEADER_ENTRY.CUST_ADDRESS1,
REMIT_ADDRESS1,
TS_REPORT_HEADER_ENTRY.CUST_ADDRESS2,
TS_REPORT_HEADER_ENTRY.CUST_ADDRESS3,
TS_REPORT_HEADER_ENTRY.CUST_ADDRESS4,
TS_REPORT_HEADER_ENTRY.CUST_CITY,
TS_REPORT_HEADER_ENTRY.CUST_STATE,
TS_REPORT_HEADER_ENTRY.CUST_POSTAL_CODE,
REMIT_ADDRESS2,
REMIT_ADDRESS3,
REMIT_ADDRESS4,
REMIT_CITY,
REMIT_STATE,
REMIT_POSTAL_CODE,
PARM_OFFICE_CHARGED);
g_error_message := 'INSERT_HEADER_info: /'||SQLERRM;
SELECT COUNT(*)
INTO LINE_COUNT
FROM RA_CUSTOMER_TRX_LINES
WHERE CUSTOMER_TRX_ID = TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID;
SELECT decode(glc.segment1,null,null,glc.segment1)||
decode(glc.segment2,null,null,'.'||glc.segment2)||
decode(glc.segment3,null,null,'.'||glc.segment3)||
decode(glc.segment4,null,null,'.'||glc.segment4)||
decode(glc.segment5,null,null,'.'||glc.segment5)||
decode(glc.segment6,null,null,'.'||glc.segment6)||
decode(glc.segment7,null,null,'.'||glc.segment7)||
decode(glc.segment8,null,null,'.'||glc.segment8)||
decode(glc.segment9,null,null,'.'||glc.segment9)||
decode(glc.segment10,null,null,'.'||glc.segment10)||
decode(glc.segment11,null,null,'.'||glc.segment11)||
decode(glc.segment12,null,null,'.'||glc.segment12)||
decode(glc.segment13,null,null,'.'||glc.segment13)||
decode(glc.segment14,null,null,'.'||glc.segment14)||
decode(glc.segment15,null,null,'.'||glc.segment15)||
decode(glc.segment16,null,null,'.'||glc.segment16)||
decode(glc.segment17,null,null,'.'||glc.segment17)||
decode(glc.segment18,null,null,'.'||glc.segment18)||
decode(glc.segment19,null,null,'.'||glc.segment19)||
decode(glc.segment20,null,null,'.'||glc.segment20)||
decode(glc.segment21,null,null,'.'||glc.segment21)||
decode(glc.segment22,null,null,'.'||glc.segment22)||
decode(glc.segment23,null,null,'.'||glc.segment23)||
decode(glc.segment24,null,null,'.'||glc.segment24)||
decode(glc.segment25,null,null,'.'||glc.segment25)||
decode(glc.segment26,null,null,'.'||glc.segment26)||
decode(glc.segment27,null,null,'.'||glc.segment27)||
decode(glc.segment28,null,null,'.'||glc.segment28)||
decode(glc.segment29,null,null,'.'||glc.segment29)||
decode(glc.segment30,null,null,'.'||glc.segment30)
INTO segment
FROM RA_CUST_TRX_LINE_GL_DIST rld,
GL_CODE_COMBINATIONS glc
WHERE rld.customer_trx_id =
TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID
AND rld.account_class = 'REC'
AND rld.code_combination_id = glc.code_combination_id;
INSERT INTO FV_SF1080_DETAIL_TEMP
(
CUSTOMER_TRX_ID,
LINE_NUMBER,
EXTENDED_AMOUNT,
SALES_ORDER,
DESCRIPTION,
QUANTITY_INVOICED,
UNIT_SELLING_PRICE,
UOM_CODE,
ACCT_OFC_RECV_FND
)
SELECT rtl.CUSTOMER_TRX_ID,
rtl.LINE_NUMBER,
rtl.EXTENDED_AMOUNT,
rtl.SALES_ORDER,
SUBSTR(rtl.DESCRIPTION,1,60),
rtl.QUANTITY_INVOICED,
rtl.UNIT_SELLING_PRICE,
rtl.UOM_CODE,
segment
FROM RA_CUSTOMER_TRX_LINES rtl
WHERE rtl.customer_trx_id =
TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID
AND rtl.line_number = line_counter;
INSERT INTO FV_SF1080_DETAIL_TEMP
(
CUSTOMER_TRX_ID,
LINE_NUMBER,
EXTENDED_AMOUNT,
SALES_ORDER,
DESCRIPTION,
QUANTITY_INVOICED,
UNIT_SELLING_PRICE,
UOM_CODE,
ACCT_OFC_RECV_FND
)
SELECT rtl.CUSTOMER_TRX_ID,
rtl.LINE_NUMBER,
rtl.EXTENDED_AMOUNT,
rtl.SALES_ORDER,
SUBSTR(rtl.DESCRIPTION,1,60),
rtl.QUANTITY_INVOICED,
rtl.UNIT_SELLING_PRICE,
rtl.UOM_CODE,
segment
FROM RA_CUSTOMER_TRX_LINES rtl
WHERE rtl.customer_trx_id =
TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID;