[Home] [Help]
4375: -- PROCEDURE GROUP_REPORT_LINES
4376: -------------------------------------------------------------------------------
4377: -- The GROUP_REPORT_LINES procedure is called from Before Report trigger of
4378: -- 1219/1220 report. The amount against each record of FV_SF1219_TEMP is rolled
4379: -- up into FV_SF1219_ORG_TEMP table for each org_id and line_id, which was
4380: -- assigned in the form of Group Name during MAIN_1219 procedure. This table is
4381: -- used for reporting 1219/1220. Record Type 'A', 'M' and 'N' are selected for
4382: -- reporting. If the record type is 'M', 'N' or 'R' (without group_name) report
4383: -- line information is selected from Manual Lines tables.
4395: v_legal_entity_id number(15) ;
4396: v_alc_code ce_bank_accounts.agency_location_code%TYPE;
4397: BEGIN
4398: -- Before inserting new records, delete any previous records from
4399: -- FV_SF1219_ORG_TEMP table
4400: DELETE FROM FV_SF1219_ORG_TEMP;
4401:
4402: COMMIT;
4403:
4396: v_alc_code ce_bank_accounts.agency_location_code%TYPE;
4397: BEGIN
4398: -- Before inserting new records, delete any previous records from
4399: -- FV_SF1219_ORG_TEMP table
4400: DELETE FROM FV_SF1219_ORG_TEMP;
4401:
4402: COMMIT;
4403:
4404: INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4400: DELETE FROM FV_SF1219_ORG_TEMP;
4401:
4402: COMMIT;
4403:
4404: INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4405: SELECT p_def_org_id, fvt.alc_code,
4406: substr(fvt.group_name,1,3),
4407: sum(fvt.amount * fvr.multiplier)
4408: FROM FV_SF1219_TEMP fvt,
4412: (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4413: AND substr(fvt.group_name,1,3) = fvr.line_id
4414: GROUP BY fvt.alc_code, substr(fvt.group_name,1,3) ;
4415:
4416: INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4417: SELECT p_def_org_id, fvt.alc_code, fvm.line_id, sum(fvt.amount *
4418: DECODE(fvt.record_type, 'N', 1,fvr.multiplier))
4419: FROM FV_SF1219_TEMP fvt,
4420: FV_SF1219_MANUAL_LINES fvm,
4425: AND fvt.temp_record_id = fvm.temp_record_id )
4426: AND fvm.line_id = fvr.line_id
4427: GROUP BY fvt.alc_code, fvm.line_id;
4428:
4429: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4430: SELECT p_def_org_id, fvt.alc_code, '410' line_id, sum(fvt.amount * -1)
4431: FROM FV_SF1219_TEMP fvt
4432: WHERE fvt.alc_code is not null
4433: AND fvt.group_name is not null
4440: SELECT alc_code INTO v_alc_code
4441: FROM fv_sf1219_temp
4442: WHERE record_type = 'P' ;
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 = (
4455: AND fvam.alc_code = DECODE(UPPER(v_alc_code),'ALL',alc_code,
4456: v_alc_code);
4457: COMMIT;
4458:
4459: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4460: SELECT p_def_org_id, alc_code, '290' line_id, sum(amount)
4461: FROM FV_SF1219_ORG_TEMP
4462: WHERE line_id in ('210','211','212','234','236','237','280')
4463: GROUP BY alc_code;
4457: COMMIT;
4458:
4459: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4460: SELECT p_def_org_id, alc_code, '290' line_id, sum(amount)
4461: FROM FV_SF1219_ORG_TEMP
4462: WHERE line_id in ('210','211','212','234','236','237','280')
4463: GROUP BY alc_code;
4464:
4465: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4461: FROM FV_SF1219_ORG_TEMP
4462: WHERE line_id in ('210','211','212','234','236','237','280')
4463: GROUP BY alc_code;
4464:
4465: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4466: SELECT p_def_org_id, alc_code, '300' line_id, sum(amount)
4467: FROM FV_SF1219_ORG_TEMP
4468: WHERE line_id in ('100','290')
4469: GROUP BY alc_code;
4463: GROUP BY alc_code;
4464:
4465: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4466: SELECT p_def_org_id, alc_code, '300' line_id, sum(amount)
4467: FROM FV_SF1219_ORG_TEMP
4468: WHERE line_id in ('100','290')
4469: GROUP BY alc_code;
4470:
4471: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4467: FROM FV_SF1219_ORG_TEMP
4468: WHERE line_id in ('100','290')
4469: GROUP BY alc_code;
4470:
4471: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4472: SELECT p_def_org_id, alc_code, '490' line_id, sum(amount)
4473: FROM FV_SF1219_ORG_TEMP
4474: WHERE line_id in ('410','420','434','436','437')
4475: GROUP BY alc_code;
4469: GROUP BY alc_code;
4470:
4471: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4472: SELECT p_def_org_id, alc_code, '490' line_id, sum(amount)
4473: FROM FV_SF1219_ORG_TEMP
4474: WHERE line_id in ('410','420','434','436','437')
4475: GROUP BY alc_code;
4476:
4477: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4473: FROM FV_SF1219_ORG_TEMP
4474: WHERE line_id in ('410','420','434','436','437')
4475: GROUP BY alc_code;
4476:
4477: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4478: SELECT p_def_org_id, alc_code, '500' line_id,
4479: sum(decode(line_id, '490',amount * -1, amount))
4480: FROM FV_SF1219_ORG_TEMP
4481: WHERE line_id in ('300','490')
4476:
4477: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4478: SELECT p_def_org_id, alc_code, '500' line_id,
4479: sum(decode(line_id, '490',amount * -1, amount))
4480: FROM FV_SF1219_ORG_TEMP
4481: WHERE line_id in ('300','490')
4482: GROUP BY alc_code;
4483:
4484: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4480: FROM FV_SF1219_ORG_TEMP
4481: WHERE line_id in ('300','490')
4482: GROUP BY alc_code;
4483:
4484: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4485: SELECT p_def_org_id, alc_code, '800' line_id, sum(amount)
4486: FROM FV_SF1219_ORG_TEMP
4487: WHERE line_id in ('610','620','650','700')
4488: GROUP BY alc_code;
4482: GROUP BY alc_code;
4483:
4484: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4485: SELECT p_def_org_id, alc_code, '800' line_id, sum(amount)
4486: FROM FV_SF1219_ORG_TEMP
4487: WHERE line_id in ('610','620','650','700')
4488: GROUP BY alc_code;
4489:
4490: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4486: FROM FV_SF1219_ORG_TEMP
4487: WHERE line_id in ('610','620','650','700')
4488: GROUP BY alc_code;
4489:
4490: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4491: SELECT p_def_org_id, alc_code, '990' line_id, sum(amount)
4492: FROM FV_SF1219_ORG_TEMP
4493: WHERE line_id in ('800','900')
4494: GROUP BY alc_code;
4488: GROUP BY alc_code;
4489:
4490: INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4491: SELECT p_def_org_id, alc_code, '990' line_id, sum(amount)
4492: FROM FV_SF1219_ORG_TEMP
4493: WHERE line_id in ('800','900')
4494: GROUP BY alc_code;
4495:
4496: EXCEPTION
4756: --WHERE record_type <> 'E';
4757:
4758: --DELETE FROM fv_sf1219_manual_lines;
4759:
4760: --DELETE FROM fv_sf1219_org_temp;
4761: --COMMIT;
4762:
4763: EXCEPTION
4764: WHEN OTHERS THEN
4856: l_module_name VARCHAR2(200) := g_module_name || 'GEN_FLAT_FILE';
4857:
4858: i NUMBER;
4859: x_line_id fv_sf1219_report_template.line_id%TYPE;
4860: x_amt fv_sf1219_org_temp.amount%TYPE;
4861:
4862: TYPE total_rec_type IS RECORD
4863: (line_id fv_sf1219_report_template.line_id%TYPE,
4864: amt fv_sf1219_org_temp.amount%TYPE);
4860: x_amt fv_sf1219_org_temp.amount%TYPE;
4861:
4862: TYPE total_rec_type IS RECORD
4863: (line_id fv_sf1219_report_template.line_id%TYPE,
4864: amt fv_sf1219_org_temp.amount%TYPE);
4865:
4866: TYPE total_tab_type IS TABLE OF total_rec_type INDEX BY BINARY_INTEGER;
4867:
4868: total_tab total_tab_type;
4872: 'A', fvo.amount,
4873: 'T', fvo.amount,
4874: 'B',fvo.amount,0)) v_amt
4875: FROM fv_sf1219_report_template fvr,
4876: fv_sf1219_org_temp fvo
4877: WHERE fvr.line_id = fvo.line_id
4878: AND fvo.alc_code = v_alc_code
4879: AND line_type <> 'D'
4880: GROUP BY fvr.line_id
4881: UNION
4882: SELECT line_id,0
4883: FROM fv_sf1219_report_template
4884: WHERE line_id NOT IN
4885: (SELECT line_id FROM fv_sf1219_org_temp
4886: WHERE alc_code = v_alc_code)
4887: AND line_type <> 'D'
4888: GROUP BY line_id;
4889:
5330: FROM fv_sf1219_temp t
5331: WHERE t.temp_record_id = m.temp_record_id
5332: AND t.record_type = 'M');
5333:
5334: DELETE FROM fv_sf1219_org_temp;
5335: COMMIT;
5336:
5337: EXCEPTION
5338: WHEN OTHERS THEN