[Home] [Help]
329: ----------------------------------------------------------------------------
330: -- PROCEDURE PURGE_TEMP_TRANSACTIONS
331: ----------------------------------------------------------------------------
332: -- If the delete_corrections parameter is 'Y' delete all records of
333: -- FV_SF1219_TEMP as well as FV_SF1219_MANUAL_LINES tables
334: -- Otherwise delete records from FV_SF1219_TEMP other than record
335: -- type 'M' and 'N'. Records types of 'M' and 'N' are not deleted as
336: -- they have been assigned report lines and should be retained.
337: ----------------------------------------------------------------------------
340: BEGIN
341: IF p_delete_corrections = 'Y'
342: THEN
343: DELETE FROM fv_sf1219_temp;
344: DELETE FROM fv_sf1219_manual_lines;
345: ELSE
346: DELETE FROM fv_sf1219_temp
347: WHERE record_type <> 'N';
348:
345: ELSE
346: DELETE FROM fv_sf1219_temp
347: WHERE record_type <> 'N';
348:
349: DELETE FROM fv_sf1219_manual_lines
350: WHERE temp_record_id NOT IN
351: (SELECT temp_record_id
352: FROM fv_sf1219_temp);
353: END IF;
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,
4421: FV_SF1219_REPORT_TEMPLATE fvr
4422: WHERE fvt.alc_code IS NOT NULL
4423: AND ((fvt.record_type IN ('M', 'N')
4424: OR (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4674: OR ((fvt.record_type = 'M' OR
4675: (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4676: AND fvt.temp_record_id IN
4677: (SELECT temp_record_id
4678: FROM fv_sf1219_manual_lines)) ) ;
4679: COMMIT;
4680:
4681:
4682: -- Set processed flag to 'Y'
4754:
4755: --DELETE FROM fv_sf1219_temp
4756: --WHERE record_type <> 'E';
4757:
4758: --DELETE FROM fv_sf1219_manual_lines;
4759:
4760: --DELETE FROM fv_sf1219_org_temp;
4761: --COMMIT;
4762:
4907: fvt.amount*fvr.multiplier))
4908: grp_amount, fvt.alc_code alc
4909: FROM fv_sf1219_report_template fvr,
4910: fv_sf1219_temp fvt,
4911: fv_sf1219_manual_lines fvm
4912: WHERE fvm.line_id = fvr.line_id
4913: AND fvt.temp_record_id = fvm.temp_record_id
4914: AND (fvt.record_type IN ('M','N') OR
4915: (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4940: to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY') acc_mon,
4941: SUM(decode(fvt.record_type,'N', fvt.amount,
4942: fvt.amount*fvr.multiplier)) grp_amt
4943: FROM fv_sf1219_report_template fvr,
4944: fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
4945: WHERE fvm.line_id = fvr.line_id
4946: AND fvt.temp_record_id = fvm.temp_record_id
4947: AND (fvt.record_type IN ('M','N') OR
4948: (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4983: fvt.amount*-1,0),
4984: 'N', DECODE(fvm.column_no, 3, fvt.amount,0),
4985: 'R', DECODE(fvm.column_no, 3, fvt.amount*-1,0))) c3,
4986: fvt.alc_code alc
4987: FROM fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
4988: WHERE fvm.temp_record_id = fvt.temp_record_id
4989: AND fvt.alc_code = v_alc_code
4990: AND fvm.line_id = '410'
4991: AND (fvt.record_type IN ('M','N') OR
5311:
5312: DELETE from fv_sf1219_temp t
5313: WHERE t.alc_code = v_alc_code
5314: AND EXISTS (SELECT 'X'
5315: FROM FV_SF1219_MANUAL_LINES m
5316: WHERE m.temp_record_id = t.temp_record_id
5317: AND t.record_type = 'M');
5318:
5319: DELETE FROM fv_sf1219_temp
5324: -- where report lines are not assigned and
5325: -- therefore not reported on the 1219/1220 report, they should
5326: -- also not be deleted.
5327:
5328: DELETE FROM fv_sf1219_manual_lines m
5329: WHERE NOT EXISTS (SELECT 'X'
5330: FROM fv_sf1219_temp t
5331: WHERE t.temp_record_id = m.temp_record_id
5332: AND t.record_type = 'M');