[Home] [Help]
192: l_aid_select := l_col_select ||
193: ' SUBSTR(c.'||l_attrib||',1,2),
194: SUBSTR(c.'||l_attrib||',3,4)
195: FROM '|| l_table||' c
196: WHERE NOT EXISTS (SELECT 1 from fv_agency_id_map m
197: where m.star_dept_reg = substr(c.'||l_attrib||',1,2)
198: and m.star_dept_reg is not null
199: and m.star_dept_xfer is null
200: )
305: l_update_statement := '
306: update hz_cust_accounts_all c
307: set TRADING_PARTNER_AGENCY_ID =
308: (select f.aid
309: from fv_agency_id_map f
310: where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
311: and f.star_dept_reg is not null
312: and f.star_dept_xfer is null
313: and rownum = 1
313: and rownum = 1
314: )
315: where exists
316: (select 1
317: from fv_agency_id_map m
318: where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
319: and m.star_dept_reg is not null
320: and m.star_dept_xfer IS NULL )
321: and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
332: l_update_statement :=
333: 'update hz_cust_accounts_all c
334: set '||l_gtas_cust_attribute||' =
335: (select f.aid
336: from fv_agency_id_map f
337: where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
338: and f.star_dept_reg is not null
339: and f.star_dept_xfer IS NULL
340: and rownum = 1
340: and rownum = 1
341: )
342: where exists
343: (select 1
344: from fv_agency_id_map m
345: where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
346: and m.star_dept_reg is not null
347: and m.star_dept_xfer IS NULL)
348: and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
362: l_update_statement :=
363: 'update ap_suppliers c
364: set global_attribute5 =
365: (select f.aid
366: from fv_agency_id_map f
367: where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
368: and f.star_dept_reg is not null
369: and f.star_dept_xfer is null
370: and rownum = 1
370: and rownum = 1
371: )
372: where exists
373: (select 1
374: from fv_agency_id_map m
375: where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
376: and m.star_dept_reg is not null
377: and m.star_dept_xfer IS NULL )
378: and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
410: SELECT f.treasury_dept_code, f.treasury_acct_code
411: FROM fv_facts_federal_accounts f
412: WHERE NOT EXISTS
413: (SELECT *
414: FROM fv_agency_id_map m, fv_treasury_symbols tas
415: WHERE tas.FEDERAL_ACCT_SYMBOL_ID = f.FEDERAL_ACCT_SYMBOL_ID
416: AND m.star_dept_reg = f.treasury_dept_code
417: AND m.star_main_acct = f.treasury_acct_code
418: AND m.star_dept_reg = tas.department_id
432: FROM fv_facts_federal_accounts f, fv_treasury_symbols t
433: WHERE f.federal_acct_symbol_id = t.federal_acct_symbol_id
434: AND NOT EXISTS
435: (SELECT 1
436: FROM fv_agency_id_map m
437: WHERE m.star_dept_reg = f.treasury_dept_code
438: AND m.star_main_acct = f.treasury_acct_code
439: AND NVL(m.star_dept_xfer,'-XX') = NVL(t.dept_transfer,'-XX')
440: AND NVL(m.SUB,'-ZZ') = NVL(t.TAFS_SUB_ACCT,'-ZZ')
446: treasury_dept_code, treasury_acct_code, f.federal_acct_symbol_id,
447: treasury_symbol_id, tafs_sub_acct, department_id, dept_transfer,
448: fund_group_code, aid,ata, f.start_date start_date, f.SET_of_books_id SET_of_books_id,
449: federal_acct_symbol_name
450: FROM fv_agency_id_map m, fv_facts_federal_accounts f, fv_treasury_symbols t
451: WHERE m.star_dept_reg = f.treasury_dept_code
452: AND m.star_main_acct = f.treasury_acct_code
453: AND f.federal_acct_symbol_id = t.federal_acct_symbol_id(+)
454: AND ((t.tafs_sub_acct IS NULL) OR (t.tafs_sub_acct IS NOT NULL AND t.tafs_sub_acct=sub));
517: -- 3)Update record to new agency id when there is a match and if only one
518: -- TAS exists for that mapping
519: IF rec.sub IS NOT NULL THEN
520: SELECT count(*) INTO cnt
521: FROM fv_agency_id_map m
522: WHERE m.star_Dept_reg = rec.star_Dept_reg
523: AND m.star_main_acct = rec.star_main_acct
524: AND m.aid <> rec.aid
525: AND (m.sub IS NOT NULL AND m.sub <> rec.sub);