[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_facts_prc_dtl;
delete_facts_prc_dtl;
TYPE aid_select_cur IS REF CURSOR;
c_aid_select aid_select_cur;
l_col_select varchar2(5000);
l_aid_select varchar2(5000);
l_update_statement varchar2(5000);
SELECT product_code
INTO l_tab_owner
FROM fnd_application
WHERE application_id = 222;
SELECT 'Y'
INTO l_cust_col_exists
FROM all_tab_columns
WHERE table_name = 'HZ_CUST_ACCOUNTS'
AND column_name = 'TRADING_PARTNER_AGENCY_ID'
AND owner = l_tab_owner;
SELECT gtas_customer_attribute
INTO l_gtas_cust_attribute
FROM fv_system_parameters;
SELECT factsI_customer_attribute, factsI_vendor_attribute
INTO l_facts1_cust_attribute, l_facts1_vend_attribute
FROM fv_system_parameters;
l_col_select := ' select c.cust_account_id, c.account_name,';
l_col_select := ' select c.vendor_id, c.vendor_name,';
l_aid_select := l_col_select ||
' SUBSTR(c.'||l_attrib||',1,2),
SUBSTR(c.'||l_attrib||',3,4)
FROM '|| l_table||' c
WHERE NOT EXISTS (SELECT 1 from fv_agency_id_map m
where m.star_dept_reg = substr(c.'||l_attrib||',1,2)
and m.star_dept_reg is not null
and m.star_dept_xfer is null
)
and substr(c.'||l_attrib||',1,2) is not null '||
l_where_clause ;
IF c_aid_select%ISOPEN THEN close c_aid_select; END IF;
OPEN c_aid_select FOR l_aid_select;
FETCH c_aid_select INTO l_account_id, l_account_name, l_agency_id, l_main_acct;
EXIT WHEN c_aid_select%NOTFOUND;
fnd_file.put_line(fnd_file.output,'Please update them manually.');
l_col_select := ' SELECT SUBSTR(c.account_name,1,80)
FROM hz_cust_accounts_all c ';
l_col_select := ' SELECT SUBSTR(c.vendor_name,1,80)
FROM ap_suppliers c ';
l_aid_select := l_col_select ||
' WHERE NVL(substr(c.'||l_attrib||',1,2),-99) IN (''48'',''95'') '||
l_where_clause ;
IF c_aid_select%ISOPEN THEN close c_aid_select; END IF;
OPEN c_aid_select FOR l_aid_select;
FETCH c_aid_select INTO l_party_name;
EXIT WHEN c_aid_select%NOTFOUND;
fnd_file.put_line(fnd_file.output,'Agency ID not updated for the following.');
l_update_statement := '
update hz_cust_accounts_all c
set TRADING_PARTNER_AGENCY_ID =
(select f.aid
from fv_agency_id_map f
where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and f.star_dept_reg is not null
and f.star_dept_xfer is null
and rownum = 1
)
where exists
(select 1
from fv_agency_id_map m
where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and m.star_dept_reg is not null
and m.star_dept_xfer IS NULL )
and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
and c.customer_class_code = ''FEDERAL''
and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
fnd_file.put_line(fnd_file.log,'Updated hz_cust_accounts_all.TRADING_PARTNER_AGENCY_ID');
l_update_statement :=
'update hz_cust_accounts_all c
set '||l_gtas_cust_attribute||' =
(select f.aid
from fv_agency_id_map f
where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and f.star_dept_reg is not null
and f.star_dept_xfer IS NULL
and rownum = 1
)
where exists
(select 1
from fv_agency_id_map m
where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and m.star_dept_reg is not null
and m.star_dept_xfer IS NULL)
and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
and c.customer_class_code = ''FEDERAL''
and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
fnd_file.put_line(fnd_file.log,'Updated hz_cust_accounts_all.'||l_gtas_cust_attribute);
EXECUTE IMMEDIATE l_update_statement;
l_update_statement :=
'update ap_suppliers c
set global_attribute5 =
(select f.aid
from fv_agency_id_map f
where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and f.star_dept_reg is not null
and f.star_dept_xfer is null
and rownum = 1
)
where exists
(select 1
from fv_agency_id_map m
where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
and m.star_dept_reg is not null
and m.star_dept_xfer IS NULL )
and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
and vendor_type_lookup_code = ''FEDERAL''
and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
fnd_file.put_line(fnd_file.log,'Updated ap_suppliers.global_attribute5');
EXECUTE IMMEDIATE l_update_statement;
l_UPDATE_fed_tas BOOLEAN := TRUE;
SELECT f.treasury_dept_code, f.treasury_acct_code
FROM fv_facts_federal_accounts f
WHERE NOT EXISTS
(SELECT *
FROM fv_agency_id_map m, fv_treasury_symbols tas
WHERE tas.FEDERAL_ACCT_SYMBOL_ID = f.FEDERAL_ACCT_SYMBOL_ID
AND m.star_dept_reg = f.treasury_dept_code
AND m.star_main_acct = f.treasury_acct_code
AND m.star_dept_reg = tas.department_id
AND m.star_main_acct = tas.fund_group_code
AND m.star_dept_xfer = tas.dept_transfer
AND m.SUB = tas.TAFS_SUB_ACCT
AND m.star_dept_reg IS NOT NULL
AND m.star_main_acct IS NOT NULL
AND m.star_dept_xfer IS NOT NULL
AND m.SUB IS NOT NULL)
AND f.treasury_dept_code IS NOT NULL
AND f.treasury_acct_code IS NOT NULL;
SELECT DISTINCT f.set_of_books_id, f.treasury_dept_code, f.treasury_acct_code,
t.TAFS_SUB_ACCT, t.dept_transfer
FROM fv_facts_federal_accounts f, fv_treasury_symbols t
WHERE f.federal_acct_symbol_id = t.federal_acct_symbol_id
AND NOT EXISTS
(SELECT 1
FROM fv_agency_id_map m
WHERE m.star_dept_reg = f.treasury_dept_code
AND m.star_main_acct = f.treasury_acct_code
AND NVL(m.star_dept_xfer,'-XX') = NVL(t.dept_transfer,'-XX')
AND NVL(m.SUB,'-ZZ') = NVL(t.TAFS_SUB_ACCT,'-ZZ')
)
ORDER BY 1, 2,3,4;
SELECT star_dept_reg, star_main_acct, star_dept_xfer, sub,
treasury_dept_code, treasury_acct_code, f.federal_acct_symbol_id,
treasury_symbol_id, tafs_sub_acct, department_id, dept_transfer,
fund_group_code, aid,ata, f.start_date start_date, f.SET_of_books_id SET_of_books_id,
federal_acct_symbol_name
FROM fv_agency_id_map m, fv_facts_federal_accounts f, fv_treasury_symbols t
WHERE m.star_dept_reg = f.treasury_dept_code
AND m.star_main_acct = f.treasury_acct_code
AND f.federal_acct_symbol_id = t.federal_acct_symbol_id(+)
AND ((t.tafs_sub_acct IS NULL) OR (t.tafs_sub_acct IS NOT NULL AND t.tafs_sub_acct=sub));
l_UPDATE_fed_tas:= TRUE;
UPDATE fv_treasury_symbols
SET dept_transfer = rec.ata
WHERE treasury_symbol_id = rec.treasury_symbol_id
AND dept_transfer = rec.star_dept_xfer
AND department_id = rec.star_dept_reg
AND fund_group_code = rec.star_main_acct;
UPDATE fv_treasury_symbols
SET dept_transfer = rec.ata
WHERE treasury_symbol_id = rec.treasury_symbol_id
AND dept_transfer = rec.star_dept_xfer
AND department_id = rec.star_dept_reg
AND fund_group_code = rec.star_main_acct
AND tafs_sub_acct = rec.sub;
SELECT count(*) INTO cnt
FROM fv_agency_id_map m
WHERE m.star_Dept_reg = rec.star_Dept_reg
AND m.star_main_acct = rec.star_main_acct
AND m.aid <> rec.aid
AND (m.sub IS NOT NULL AND m.sub <> rec.sub);
SELECT count(*) INTO cnt1 FROM fv_Treasury_symbols
WHERE treasury_symbol_id <> rec.treasury_symbol_id
AND department_id = rec.star_dept_reg
AND fund_group_code = rec.star_main_acct
AND tafs_sub_acct <> rec.sub;
select FEDERAL_ACCT_SYMBOL_ID into l_FEDERAL_ACCT_SYMBOL_ID
from fv_facts_federal_accounts
where treasury_dept_code = rec.aid
and treasury_acct_code = rec.star_main_acct;
UPDATE fv_treasury_symbols
SET department_id = rec.aid,
federal_acct_symbol_id = l_FEDERAL_ACCT_SYMBOL_ID
WHERE treasury_symbol_id = rec.treasury_symbol_id;
l_UPDATE_fed_tas := false;
SELECT FV_FACTS_FEDERAL_ACCOUNTS_S.nextval INTO l_federal_seq FROM dual;
INSERT INTO FV_FACTS_FEDERAL_ACCounTS
(FEDERAL_ACCT_SYMBOL_ID,
SET_OF_BOOKS_ID,
TREASURY_DEPT_CODE,
TREASURY_ACCT_CODE,
FEDERAL_ACCT_SYMBOL_NAME,
START_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(l_federal_seq,
rec.SET_OF_BOOKS_ID,
rec.aid,
rec.star_main_acct,
rec.FEDERAL_ACCT_SYMBOL_NAME ,
rec.START_DATE,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id);
UPDATE fv_treasury_symbols
SET department_id = rec.aid,
federal_acct_symbol_id = l_federal_seq
WHERE treasury_symbol_id = rec.treasury_symbol_id;
l_UPDATE_fed_tas := false;
IF (l_UPDATE_fed_tas) THEN
SELECT count(*) INTO cnt
FROM fv_facts_federal_accounts
WHERE treasury_dept_code = rec.aid
AND treasury_acct_code = rec.treasury_acct_code
AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
SELECT federal_acct_symbol_id INTO l_federal_acct_symbol_id
FROM fv_facts_federal_accounts
WHERE treasury_dept_code = rec.aid
AND treasury_acct_code = rec.treasury_acct_code
AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
UPDATE Fv_Treasury_Symbols
SET federal_acct_symbol_id = l_federal_acct_symbol_id,
department_id = rec.aid
WHERE Treasury_Symbol_Id = rec.Treasury_Symbol_Id;
UPDATE fv_facts_federal_accounts
SET treasury_dept_code = rec.aid
WHERE federal_acct_symbol_id = rec.federal_acct_symbol_id;
UPDATE fv_treasury_symbols
SET department_id = rec.aid
WHERE federal_acct_symbol_id = rec.federal_acct_symbol_id
AND treasury_symbol_id = rec.treasury_symbol_id;
fnd_file.put_line(fnd_file.log, 'Updated: '||SQL%ROWCOUNT||' rows.');
DELETE FROM fv_facts_bud_fed_accts;
DELETE FROM fv_facts_budget_accounts;
fnd_file.put_line(fnd_file.log, 'Deleted all records FROM fv_facts_bud_fed_accts AND fv_facts_budget_accounts');
UPDATE ap_suppliers
SET global_attribute4 = 'F',
global_attribute_category = 'FV.US.APXVDMVD.GTAS_DATA',
global_attribute20 = 1
WHERE vendor_type_lookup_code = 'FEDERAL';
UPDATE ap_suppliers
SET global_attribute4 = 'N',
global_attribute_category = 'FV.US.APXVDMVD.GTAS_DATA',
global_attribute20 = 1
WHERE vendor_type_lookup_code <> 'FEDERAL'
AND global_attribute_category IS NULL;
fnd_file.put_line(fnd_file.log, 'Updated Fed Non-Fed Code in ap_suppliers.');
UPDATE fv_fund_parameters
SET fund_category = 'E'
WHERE fund_category = 'C';
UPDATE fv_treasury_symbols
SET fund_type = 'EG'
WHERE (fund_group_code BETWEEN 0000 AND 3499 OR
fund_group_code BETWEEN 3600 AND 3799)
AND (receipt_account_indicator = 'N' OR
receipt_account_indicator IS NULL);
UPDATE fv_treasury_symbols
SET fund_type = 'UG'
WHERE (fund_group_code BETWEEN 0000 AND 3499 OR
fund_group_code BETWEEN 3600 AND 3799)
AND receipt_account_indicator = 'Y';
UPDATE fv_treasury_symbols
SET fund_type = 'ES'
WHERE fund_group_code BETWEEN 5000 AND 5999
AND (receipt_account_indicator = 'N' OR receipt_account_indicator IS NULL);
UPDATE fv_treasury_symbols
SET fund_type = 'US'
WHERE fund_group_code BETWEEN 5000 AND 5999
AND receipt_account_indicator = 'Y';
UPDATE fv_treasury_symbols
SET fund_type = 'EP'
WHERE fund_group_code BETWEEN 4000 AND 4499;
UPDATE fv_treasury_symbols
SET fund_type = 'ER'
WHERE fund_group_code BETWEEN 4500 AND 4999;
UPDATE fv_treasury_symbols
SET fund_type = 'EM'
WHERE fund_group_code BETWEEN 3900 AND 3959;
UPDATE fv_treasury_symbols
SET fund_type = 'EC'
WHERE fund_group_code BETWEEN 3960 AND 3999;
UPDATE fv_treasury_symbols
SET fund_type = 'DF'
WHERE fund_group_code BETWEEN 6000 AND 6999;
UPDATE fv_treasury_symbols
SET fund_type = 'ET'
WHERE (fund_group_code BETWEEN 8000 AND 8399)
OR (fund_group_code BETWEEN 8500 AND 8999)
AND (receipt_account_indicator = 'N' OR receipt_account_indicator IS NULL);
UPDATE fv_treasury_symbols
SET fund_type = 'UT'
WHERE (fund_group_code BETWEEN 8000 AND 8399)
OR (fund_group_code BETWEEN 8500 AND 8999)
AND receipt_account_indicator = 'Y';
UPDATE fv_treasury_symbols
SET fund_type = 'TR'
WHERE fund_group_code BETWEEN 8400 AND 8499;
UPDATE fv_treasury_symbols
SET fund_type = 'CF'
WHERE (fund_group_code BETWEEN 3500 AND 3599 OR
fund_group_code BETWEEN 3800 AND 3899);
FOR rec IN (SELECT TREASURY_SYMBOL
FROM FV_TP_TREASURY_SYMBOLS
WHERE treasury_symbol_id IS NULL)
LOOP
SELECT fv_treasury_symbols_s.nextval INTO tpTASSeq from dual;
UPDATE fv_tp_treasury_symbols
SET treasury_symbol_id = tpTASSeq,
set_of_books_id = sob,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE treasury_symbol = rec.treasury_symbol;
FOR rec IN (SELECT TREASURY_SYMBOL,
BETC_CODE,
DEFAULT_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY
FROM FV_TP_TAS_BETC_MAP)
LOOP
SELECT treasury_symbol_id INTO tpTASSeq FROM fv_tp_treasury_symbols
WHERE treasury_symbol = rec.treasury_symbol;
INSERT INTO fv_tas_betc_map(TREASURY_SYMBOL_ID,
SET_OF_BOOKS_ID,
BETC_CODE,
DEFAULT_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
TAS_TYPE)
VALUES(tpTASSeq,
sob,
rec.BETC_CODE,
rec.DEFAULT_FLAG,
rec.ATTRIBUTE1,
rec.ATTRIBUTE2,
rec.ATTRIBUTE3,
rec.ATTRIBUTE4,
rec.ATTRIBUTE5,
rec.ATTRIBUTE6,
rec.ATTRIBUTE7,
rec.ATTRIBUTE8,
rec.ATTRIBUTE9,
rec.ATTRIBUTE10,
rec.ATTRIBUTE11,
rec.ATTRIBUTE12,
rec.ATTRIBUTE13,
rec.ATTRIBUTE14,
rec.ATTRIBUTE15,
rec.ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
'T');
UPDATE fv_treasury_symbols
SET time_frame = 'R',
availability_type_code = NULL
WHERE receipt_account_indicator = 'Y';
UPDATE fv_treasury_symbols
SET time_frame = 'F',
availability_type_code = 'F'
WHERE time_frame = 'NO_YEAR'
AND (fund_group_code BETWEEN 3500 AND 3599 OR
fund_group_code BETWEEN 3800 AND 3899);
UPDATE fv_treasury_symbols
SET time_frame = 'X',
availability_type_code = 'X'
WHERE (receipt_account_indicator = 'N' OR
receipt_account_indicator IS NULL)
AND time_frame = 'NO_YEAR'
AND (fund_group_code BETWEEN 0000 AND 3499 OR
fund_group_code BETWEEN 3600 AND 3799);
UPDATE fv_fund_parameters
SET outlays_bea_category_code = bea_category
WHERE outlays_bea_category_code IS NULL;
UPDATE fv_treasury_symbols
SET gtas_reportable_indicator ='Y'
WHERE FACTS_REPORTABLE_INDICATOR IS NOT NULL;
DELETE FROM fv_facts_bud_fed_accts;
DELETE FROM fv_facts_budget_accounts;
PROCEDURE DELETE_FACTS_PRC_DTL IS
l_module VARCHAR2(1000) := g_module||'DELETE_FACTS_PRC_DTL';
SELECT product_code
INTO l_event_owner
FROM fnd_application
WHERE application_id = 8901;
DELETE FROM FV_FACTS_PRC_DTL;
END DELETE_FACTS_PRC_DTL;