[Home] [Help]
557:
558:
559: l_string3 := 'AND NOT EXISTS
560: (SELECT ''X''
561: FROM fv_sf1219_audits fvs
562: WHERE fvs.batch_id = glb.je_batch_id
563: AND fvs.je_header_id = gll.je_header_id
564: AND fvs.je_line_num = gll.je_line_num
565: AND fvs.record_type <> ''B'')';
3894: -- Get the supplement Number for the alc_code and period
3895: UPDATE fv_sf1219_temp fst
3896: SET supplement_number =
3897: (SELECT NVL(MAX(supplement_number), -1) + 1
3898: FROM fv_sf1219_audits fsa
3899: WHERE fst.alc_code = fsa.alc_code
3900: AND gl_period = p_gl_period);
3901:
3902: COMMIT;
4443:
4444: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4445: SELECT p_def_org_id, fvam.alc_code, '100'
4446: line_id, fvam.accountability_balance
4447: FROM FV_SF1219_AUDITS fvam
4448: WHERE fvam.reported_gl_period = (
4449: select to_char(max(
4450: to_date(fvas.reported_gl_period,'MM-YYYY')),'MMYYYY')
4451: from fv_sf1219_audits fvas
4447: FROM FV_SF1219_AUDITS fvam
4448: WHERE fvam.reported_gl_period = (
4449: select to_char(max(
4450: to_date(fvas.reported_gl_period,'MM-YYYY')),'MMYYYY')
4451: from fv_sf1219_audits fvas
4452: where fvas.alc_code = fvam.alc_code
4453: and fvas.record_type = 'B' )
4454: AND fvam.record_type = 'B'
4455: AND fvam.alc_code = DECODE(UPPER(v_alc_code),'ALL',alc_code,
4509: -- PROCEDURE INSERT_AUDIT_TABLE
4510: -------------------------------------------------------------------------------
4511: -- The INSERT_AUDIT_TABLE procedure is called from After Report trigger of
4512: -- 1219/1220 report, only when report is run in Final mode. The procedure
4513: -- inserts records from FV_SF1219_TEMP table to FV_SF1219_AUDITS table, which
4514: -- have reported_month as 'CURRENT' or 'CURRENT / PRIOR', and org id is not null.
4515: -- These batches are excluded for any subsequent run.
4516: -- Also, it sets the processed_flag to 'Y' for fv_interagency_funds_all and
4517: -- fv_refunds_voids_all tables.
4608: -- If the number goes beyond 3 then print a line in the log file
4609: -- indicating that the supplement number has reached 3.
4610: SELECT NVL(MAX(supplement_number),-1) + 1
4611: INTO v_supp_number
4612: FROM fv_sf1219_audits
4613: WHERE alc_code = v_alc_code
4614: AND reported_gl_period = l_reported_period;
4615:
4616: IF v_supp_number > 3
4625: -- are moved to Audit table in final mode.
4626: -- If any one of group_name and alc_code is null that record
4627: -- is not moved to Audit table
4628:
4629: INSERT INTO fv_sf1219_audits (
4630: batch_id,
4631: sf1219_type_code,
4632: exception_category,
4633: gl_period,
4794: -- Parameter p_rep_gl_period needs to be in varchar2(6) 'MMYYYY' format.
4795:
4796: l_module_name := g_module_name || 'INSERT_ACCOUNTABILITY_BALANCE';
4797:
4798: UPDATE FV_SF1219_AUDITS
4799: set accountability_balance = p_cl_balance,
4800: last_update_date = sysdate,
4801: last_updated_by = FND_GLOBAL.USER_ID,
4802: last_update_login = FND_GLOBAL.LOGIN_ID
4804: and alc_code = p_alc_code
4805: and record_type = 'B' ;
4806:
4807: IF SQL%NOTFOUND then
4808: INSERT into FV_SF1219_AUDITS (
4809: batch_id,
4810: reported_gl_period,
4811: accountability_balance,
4812: alc_code,
5049:
5050: BEGIN
5051: SELECT MAX(supplement_number)
5052: INTO max_supplement_number
5053: FROM fv_sf1219_audits
5054: WHERE alc_code = v_alc_code
5055: AND reported_gl_period = l_reported_period;
5056: EXCEPTION
5057: WHEN OTHERS THEN