[Home] [Help]
513:
514: DELETE je_it_trx_lines_all
515: WHERE REQUEST_ID = l_conc_request_id;
516:
517: DELETE je_it_trx_above_thr_all
518: WHERE REQUEST_ID = l_conc_request_id;
519:
520: DELETE je_it_above_thr_hdr_all
521: WHERE REQUEST_ID = l_conc_request_id;
1798: trx_gl_date,
1799: trx_id,
1800: contract_identification,
1801: below_threshold_flag
1802: FROM je_it_trx_above_thr_all
1803: WHERE request_id = g_conc_request_id
1804: ORDER BY record_type,
1805: application_id desc,
1806: party_name,
1812:
1813: BEGIN
1814: debug_message('Start of Insert_Above_Threshold_Lines');
1815: BEGIN
1816: INSERT INTO je_it_trx_above_thr_all
1817: (
1818: request_id ,
1819: vat_reporting_entity_id,
1820: org_id,
1994: END;
1995:
1996: BEGIN -- For Contract Identification, update the trx_gl_date with the latest invoice gl_date
1997: debug_message('Update for trx_gl_date with the latest invoice gl_date');
1998: UPDATE je_it_trx_above_thr_all trx
1999: SET trx_gl_date = (SELECT MAX(trx_gl_date)
2000: FROM je_it_trx_lines_all trx_lines
2001: WHERE request_id = g_conc_request_id
2002: AND trx_lines.contract_identification = trx.contract_identification
2020: END;
2021:
2022: BEGIN -- For Contract Identification, update the trx_number with the latest invoice number
2023: debug_message('Update the trx_number with the latest invoice number');
2024: UPDATE je_it_trx_above_thr_all trx
2025: SET trx_num = (SELECT MAX(trx_num)
2026: FROM je_it_trx_lines_all trx_lines
2027: WHERE request_id = g_conc_request_id
2028: AND trx_lines.contract_identification = trx.contract_identification
2050:
2051: -- Update trx_num,trx_date,trx_gl_date,trx_payment_method,trx_accounting_status for transactions fall
2052: -- under normal invoice grouping.
2053: debug_message('Update for normal invoice grouping');
2054: UPDATE je_it_trx_above_thr_all a
2055: SET (trx_num,trx_date,trx_gl_date,
2056: payment_method,trx_accounting_status) = (SELECT MAX(trx_num),MAX(trx_date),
2057: MAX(trx_gl_date),MAX(payment_method),
2058: MAX(trx_accounting_status)
2067:
2068:
2069: For rec IN update_seq_no_cur
2070: LOOP
2071: UPDATE je_it_trx_above_thr_all
2072: SET eft_progression_num = decode(mod(i,g_max_no_of_records),0,trunc(i/g_max_no_of_records),trunc(i/g_max_no_of_records)+1),
2073: eft_sequential_num =decode(mod(i,g_max_no_of_records),0,g_max_no_of_records,mod(i,g_max_no_of_records))
2074: WHERE request_id = g_conc_request_id
2075: AND record_type = rec.record_type
2088: -- Required to derive eft_transmission_number and sequential_number
2089:
2090: SELECT MAX(eft_progression_num)
2091: INTO l_prog_cnt
2092: FROM je_it_trx_above_thr_all
2093: WHERE request_id = g_conc_request_id;
2094:
2095: SELECT MAX(eft_sequential_num)
2096: INTO l_seq_cnt
2093: WHERE request_id = g_conc_request_id;
2094:
2095: SELECT MAX(eft_sequential_num)
2096: INTO l_seq_cnt
2097: FROM je_it_trx_above_thr_all
2098: WHERE request_id = g_conc_request_id
2099: AND eft_progression_num = l_prog_cnt;
2100:
2101: l_std_inv_cnt := ((l_prog_cnt-1) * g_max_no_of_records) + l_seq_cnt;
2114: END;
2115:
2116: -- Insert record type 4,5 information.
2117: BEGIN
2118: INSERT INTO je_it_trx_above_thr_all (request_id,
2119: vat_reporting_entity_id,
2120: org_id,
2121: application_id,
2122: record_type,
2247: END;
2248:
2249: -- Update original trx_id information. original invoice information may exist in previous declaration years also.
2250: BEGIN
2251: UPDATE je_it_trx_above_thr_all trx
2252: SET (orig_trx_num,orig_trx_date,
2253: orig_trx_gl_date,orig_trx_acctg_status,
2254: orig_trx_payment_method) = (SELECT MAX(trx_num),NVL(MAX(trx_date),g_end_date),NVL(MAX(trx_gl_date),g_end_date),
2255: MAX(trx_lines.trx_accounting_status),MAX(payment_method)
2299: l_exist := 0;
2300:
2301: SELECT count(1)
2302: INTO l_exist
2303: FROM je_it_trx_above_thr_all
2304: WHERE request_id = p_concurrent_request_id
2305: AND eft_progression_num = p_progression_number+1;
2306:
2307: debug_message('l_exist :'|| l_exist);