[Home] [Help]
137: Begin
138: SELECT owner
139: INTO g_table_schema
140: FROM all_tables
141: WHERE table_name = 'JE_IT_TRX_LINES_ALL'
142: AND OWNER = l_schema_name;
143: EXCEPTION
144: WHEN NO_DATA_FOUND THEN
145: g_table_schema := 'JE';
362: return;
363: END;
364: IF p_type_of_upload <> 2 THEN -- If type of upload is 'Cancellation' then no need to run the extract.
365:
366: Extract_AP_Trx_data; -- Extract AP data and inserts into je_it_trx_lines_all table.
367: IF g_retcode = 2 THEN
368: errbuf := g_errbuf;
369: retcode := 2;
370: return;
510: AND type_of_upload = p_type_of_upload;
511:
512: debug_message('after request_id -'||l_conc_request_id );
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;
544:
545: BEGIN
546: debug_message('Start Of Extract_ap_trx_data');
547:
548: INSERT INTO je_it_trx_lines_all (REQUEST_ID,
549: VAT_REPORTING_ENTITY_ID,
550: ORG_ID,
551: APPLICATION_ID,
552: PARTY_ID,
758: -- then it will be taken care by check_missing_invoice proc.(orig invoice missing error)
759:
760: --Added for Performance -Start
761: commit;
762: fnd_stats.gather_table_stats(g_table_schema,'JE_IT_TRX_LINES_ALL',PERCENT=>30);
763: --Added for Performance -End
764:
765: debug_message('Updating gdf information for AP Tax lines');
766:
766:
767: BEGIN
768: /*
769: --Changed for performace
770: UPDATE je_it_trx_lines_all trx_lines
771: SET (payment_mode,
772: below_threshold_flag,
773: report_exclusion_flag,
774: contract_identification,
781: contract_identification,
782: adj_inv_flag,
783: orig_trx_id,
784: inconst_cm_dm_appl_flag
785: FROM je_it_trx_lines_all item_lines
786: WHERE item_lines.trx_id = trx_lines.trx_id
787: AND item_lines.REQUEST_ID = g_conc_request_id
788: AND item_lines.trx_line_id = (SELECT adl.charge_applicable_to_dist_id FROM
789: ap_invoice_distributions_all adl
794: and trx_lines.trx_line_type like '%TAX'
795: AND trx_lines.REQUEST_ID = g_conc_request_id;
796: */
797:
798: UPDATE je_it_trx_lines_all trx_lines
799: SET (payment_mode,
800: below_threshold_flag,
801: report_exclusion_flag,
802: contract_identification,
810: contract_identification,
811: adj_inv_flag,
812: orig_trx_id,
813: inconst_cm_dm_appl_flag
814: FROM je_it_trx_lines_all item_lines,
815: ap_invoice_distributions_all adl
816: WHERE item_lines.trx_id = trx_lines.trx_id
817: AND item_lines.request_id = g_conc_request_id
818: AND item_lines.trx_line_id = adl.charge_applicable_to_dist_id
835: END;
836:
837: BEGIN
838:
839: UPDATE je_it_trx_lines_all
840: SET record_type = DECODE(country,'IT',2,3)
841: WHERE application_id = 200
842: AND request_id = g_conc_request_id
843: AND trx_type <> 'CREDIT'
843: AND trx_type <> 'CREDIT'
844: AND trx_type <> 'DEBIT'
845: AND adj_inv_flag = 'N';
846:
847: UPDATE je_it_trx_lines_all a
848: SET record_type = DECODE(country,'IT',2,3)
849: WHERE application_id = 200
850: AND request_id = g_conc_request_id
851: AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
850: AND request_id = g_conc_request_id
851: AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
852: AND a.orig_trx_id IS NOT NULL
853: AND exists (SELECT 1
854: FROM je_it_trx_lines_all b
855: WHERE b.request_id = g_conc_request_id
856: AND b.application_id = 200
857: AND b.trx_id = a.orig_trx_id);
858:
855: WHERE b.request_id = g_conc_request_id
856: AND b.application_id = 200
857: AND b.trx_id = a.orig_trx_id);
858:
859: UPDATE je_it_trx_lines_all a
860: SET a.record_type = DECODE(a.country,'IT',4,5)
861: WHERE a.application_id = 200
862: AND a.request_id = g_conc_request_id
863: AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
862: AND a.request_id = g_conc_request_id
863: AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
864: AND (a.orig_trx_id IS NULL
865: OR EXISTS (SELECT 1
866: FROM je_it_trx_lines_all b,
867: je_it_above_thr_hdr_all h
868: WHERE h.year_of_declaration in (g_year-1,g_year-2)
869: AND h.request_id = b.request_id
870: AND b.application_id = 200
1011:
1012: PROCEDURE Extract_AR_Trx_data IS
1013: cursor c_update_gdf(p_request_id NUMBER)
1014: is select trx_id,trx_line_id from
1015: je_it_trx_lines_all
1016: where application_id = 222
1017: and trx_line_type = 'TAX'
1018: AND REQUEST_ID = p_request_id;
1019: l_api_name VARCHAR2(30) := 'Extract_AR_Trx_Data';
1021:
1022: debug_message('Start of Extract_AR_Trx_data');
1023: -- For performance Expand SQL into two separate statements UNION together.
1024: BEGIN
1025: INSERT INTO je_it_trx_lines_all (request_id,
1026: vat_reporting_entity_id,
1027: org_id,
1028: application_id,
1029: party_id,
1345: END;
1346:
1347: --Added for Performance -Start
1348: commit;
1349: fnd_stats.gather_table_stats(g_table_schema,'JE_IT_TRX_LINES_ALL',PERCENT=>30);
1350: --Added for Performance -End
1351: debug_message('Updating gdf information for AR Tax lines');
1352:
1353: BEGIN
1352:
1353: BEGIN
1354: /*
1355: --Changed for performace
1356: UPDATE je_it_trx_lines_all trx_lines
1357: SET (payment_mode,
1358: below_threshold_flag,
1359: report_exclusion_flag,
1360: contract_identification,
1367: contract_identification,
1368: adj_inv_flag,
1369: orig_trx_id,
1370: inconst_cm_dm_appl_flag
1371: FROM je_it_trx_lines_all item_lines
1372: WHERE item_lines.trx_id = trx_lines.trx_id
1373: AND item_lines.REQUEST_ID = g_conc_request_id
1374: AND item_lines.trx_line_id = (SELECT rctl.link_to_cust_trx_line_id FROM
1375: ra_customer_trx_lines_all rctl
1380: and trx_lines.trx_line_type = 'TAX'
1381: AND trx_lines.REQUEST_ID = g_conc_request_id;
1382: --Changed for performace
1383: */
1384: UPDATE je_it_trx_lines_all trx_lines
1385: SET (payment_mode,
1386: below_threshold_flag,
1387: report_exclusion_flag,
1388: contract_identification,
1397: contract_identification,
1398: adj_inv_flag,
1399: orig_trx_id,
1400: inconst_cm_dm_appl_flag
1401: FROM je_it_trx_lines_all item_lines
1402: ,ra_customer_trx_lines_all rctl
1403: WHERE item_lines.trx_id = trx_lines.trx_id
1404: AND item_lines.REQUEST_ID = g_conc_request_id
1405: AND item_lines.trx_line_id = rctl.link_to_cust_trx_line_id
1427: -- Update GL Date. GL Date should be transaction's header GL_DATE. below logic derived from RA_CUSTOMER_TRX_PARTIAL_V
1428: -- As this view is the source for AR Transaction's header block in Transaction Workbench.
1429: BEGIN
1430:
1431: UPDATE je_it_trx_lines_all trx_lines
1432: SET trx_lines.trx_gl_date = (SELECT gl_date
1433: FROM ra_cust_trx_line_gl_dist_all dist
1434: WHERE dist.customer_trx_id = trx_lines.trx_id
1435: AND dist.account_class = 'REC'
1457: -- country='IT' and vat_reg_num is null : I think cm/dm doesn't exist.even if exists leave them as it shouldn't be shown in 4th or 5th record type. For other trxs, record type is 1
1458: -- country <> 'IT' then for cm/dm, record type is 5. For other trx, record type is 3
1459: BEGIN
1460:
1461: UPDATE je_it_trx_lines_all
1462: SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
1463: WHERE application_id = 222
1464: AND request_id = g_conc_request_id
1465: AND trx_type <> 'CM'
1465: AND trx_type <> 'CM'
1466: AND trx_type <> 'DM'
1467: AND adj_inv_flag = 'N';
1468:
1469: UPDATE je_it_trx_lines_all a
1470: SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
1471: WHERE application_id = 222
1472: AND request_id = g_conc_request_id
1473: AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1472: AND request_id = g_conc_request_id
1473: AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1474: AND a.orig_trx_id IS NOT NULL
1475: AND exists (SELECT 1
1476: FROM je_it_trx_lines_all b
1477: WHERE b.request_id = g_conc_request_id
1478: AND b.application_id = 222
1479: AND b.trx_id = a.orig_trx_id);
1480:
1477: WHERE b.request_id = g_conc_request_id
1478: AND b.application_id = 222
1479: AND b.trx_id = a.orig_trx_id);
1480:
1481: UPDATE je_it_trx_lines_all a
1482: SET a.record_type = DECODE(a.country,'IT',4,5)
1483: WHERE a.application_id = 222
1484: AND a.request_id = g_conc_request_id
1485: AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1484: AND a.request_id = g_conc_request_id
1485: AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
1486: AND (a.orig_trx_id IS NULL
1487: OR EXISTS (SELECT 1
1488: FROM je_it_trx_lines_all b,
1489: je_it_above_thr_hdr_all h
1490: WHERE h.year_of_declaration in (g_year-1,g_year-2)
1491: AND h.request_id = b.request_id
1492: AND b.application_id = 222
1516: --Check if a transaction has multiple payment mode values. If it has then update the PAYMENT_MODE_ERR_FLAG with 'Y'
1517: debug_message('Start of CHECK_PAYMENT_MODE_ERROR');
1518: BEGIN
1519:
1520: UPDATE je_it_trx_lines_all
1521: SET PAYMENT_MODE_ERR_FLAG = 'Y'
1522: WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1523: FROM je_it_trx_lines_all
1524: WHERE request_id = g_conc_request_id
1519:
1520: UPDATE je_it_trx_lines_all
1521: SET PAYMENT_MODE_ERR_FLAG = 'Y'
1522: WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1523: FROM je_it_trx_lines_all
1524: WHERE request_id = g_conc_request_id
1525: AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1526: GROUP BY application_id,trx_id
1527: HAVING COUNT(DISTINCT payment_mode) > 1)
1531: -- IF the original transaction is inconsistent (having different payment modes), then the
1532: -- credit/debit memos which got applied on that orginal transaction should considered as
1533: -- inconsistent application. (As the application is not valid)
1534:
1535: UPDATE je_it_trx_lines_all a
1536: SET inconst_cm_dm_appl_flag = 'Y'
1537: WHERE request_id = g_conc_request_id
1538: AND exists (SELECT 1
1539: FROM je_it_trx_lines_all b
1535: UPDATE je_it_trx_lines_all a
1536: SET inconst_cm_dm_appl_flag = 'Y'
1537: WHERE request_id = g_conc_request_id
1538: AND exists (SELECT 1
1539: FROM je_it_trx_lines_all b
1540: WHERE b.request_id = g_conc_request_id
1541: AND b.trx_id = a.orig_trx_id
1542: AND b.payment_mode_err_flag = 'Y');
1543:
1557: --Check if a Contract Identification has multiple payment mode values. If it has update the PAYMENT_MODE_ERR_FLAG with 'Y'
1558:
1559: BEGIN
1560:
1561: UPDATE je_it_trx_lines_all
1562: SET PAYMENT_MODE_ERR_FLAG = 'Y'
1563: WHERE (application_id,party_id,contract_identification) IN (SELECT application_id,party_id,contract_identification
1564: FROM je_it_trx_lines_all
1565: WHERE request_id = g_conc_request_id
1560:
1561: UPDATE je_it_trx_lines_all
1562: SET PAYMENT_MODE_ERR_FLAG = 'Y'
1563: WHERE (application_id,party_id,contract_identification) IN (SELECT application_id,party_id,contract_identification
1564: FROM je_it_trx_lines_all
1565: WHERE request_id = g_conc_request_id
1566: AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1567: GROUP BY application_id,party_id,contract_identification
1568: HAVING COUNT(DISTINCT payment_mode) > 1)
1590: --Check if a transaction has multiple payment mode values. If it has then update the PAYMENT_MODE_ERR_FLAG with 'Y'
1591: debug_message('Start of CHECK_PARTIAL_ADJ_INV');
1592: BEGIN
1593:
1594: UPDATE je_it_trx_lines_all
1595: SET partial_adj_inv_flag = 'Y'
1596: WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1597: FROM je_it_trx_lines_all
1598: WHERE request_id = g_conc_request_id
1593:
1594: UPDATE je_it_trx_lines_all
1595: SET partial_adj_inv_flag = 'Y'
1596: WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
1597: FROM je_it_trx_lines_all
1598: WHERE request_id = g_conc_request_id
1599: AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
1600: GROUP BY application_id,trx_id
1601: HAVING COUNT(DISTINCT adj_inv_flag) > 1)
1620:
1621: BEGIN
1622: debug_message('Start of Check_missing_invoice');
1623: /* --Changed for performace
1624: UPDATE je_it_trx_lines_all
1625: SET ORIG_TRX_MISSING_FLAG = 'Y'
1626: WHERE (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1627: FROM je_it_trx_lines_all
1628: WHERE request_id = g_conc_request_id
1623: /* --Changed for performace
1624: UPDATE je_it_trx_lines_all
1625: SET ORIG_TRX_MISSING_FLAG = 'Y'
1626: WHERE (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1627: FROM je_it_trx_lines_all
1628: WHERE request_id = g_conc_request_id
1629: )
1630: AND (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1631: FROM je_it_trx_lines_all trx_lines,
1627: FROM je_it_trx_lines_all
1628: WHERE request_id = g_conc_request_id
1629: )
1630: AND (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
1631: FROM je_it_trx_lines_all trx_lines,
1632: je_it_above_thr_hdr_all hdr
1633: WHERE trx_lines.request_id = hdr.request_id
1634: AND hdr.year_of_declaration in (g_year-1,g_year-2)
1635: AND is_above_threshold = 'Y')
1636: AND request_id = g_conc_request_id;
1637: */
1638:
1639: --Changed for Performance -Start
1640: UPDATE je_it_trx_lines_all
1641: SET orig_trx_missing_flag = 'Y'
1642: WHERE rowid NOT IN
1643: (SELECT t2.rowid
1644: FROM je_it_trx_lines_all t1,
1640: UPDATE je_it_trx_lines_all
1641: SET orig_trx_missing_flag = 'Y'
1642: WHERE rowid NOT IN
1643: (SELECT t2.rowid
1644: FROM je_it_trx_lines_all t1,
1645: je_it_trx_lines_all t2
1646: WHERE t1.request_id = g_conc_request_id
1647: AND t2.request_id = g_conc_request_id
1648: AND t2.application_id = t1.application_id
1641: SET orig_trx_missing_flag = 'Y'
1642: WHERE rowid NOT IN
1643: (SELECT t2.rowid
1644: FROM je_it_trx_lines_all t1,
1645: je_it_trx_lines_all t2
1646: WHERE t1.request_id = g_conc_request_id
1647: AND t2.request_id = g_conc_request_id
1648: AND t2.application_id = t1.application_id
1649: AND t2.orig_trx_id = t1.trx_id
1649: AND t2.orig_trx_id = t1.trx_id
1650: AND t2.orig_trx_id IS NOT NULL
1651: UNION
1652: SELECT t2.rowid
1653: FROM je_it_trx_lines_all t2,
1654: je_it_trx_lines_all trx_lines,
1655: je_it_above_thr_hdr_all hdr
1656: WHERE trx_lines.request_id = hdr.request_id
1657: AND hdr.year_of_declaration IN(g_year -1,g_year-2)
1650: AND t2.orig_trx_id IS NOT NULL
1651: UNION
1652: SELECT t2.rowid
1653: FROM je_it_trx_lines_all t2,
1654: je_it_trx_lines_all trx_lines,
1655: je_it_above_thr_hdr_all hdr
1656: WHERE trx_lines.request_id = hdr.request_id
1657: AND hdr.year_of_declaration IN(g_year -1,g_year-2)
1658: AND trx_lines.is_above_threshold = 'Y'
1681: BEGIN
1682:
1683: debug_message('Start of Update_Above_Threshold_Lines');
1684:
1685: UPDATE je_it_trx_lines_all a -- For normal grouping
1686: SET a.is_above_threshold = 'Y'
1687: WHERE a.request_id = g_conc_request_id
1688: AND (a.application_id,NVL(a.ORIG_TRX_ID,a.trx_id)) IN (SELECT b.application_id,
1689: NVL(b.ORIG_TRX_ID,b.trx_id)
1686: SET a.is_above_threshold = 'Y'
1687: WHERE a.request_id = g_conc_request_id
1688: AND (a.application_id,NVL(a.ORIG_TRX_ID,a.trx_id)) IN (SELECT b.application_id,
1689: NVL(b.ORIG_TRX_ID,b.trx_id)
1690: FROM je_it_trx_lines_all b
1691: WHERE b.request_id = g_conc_request_id
1692: AND b.below_threshold_flag = 'N'
1693: AND NVL(b.report_exclusion_flag,'N') = 'N'
1694: AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
1695: AND NVL(b.payment_mode_err_flag,'N') = 'N'
1696: AND NVL(b.orig_trx_missing_flag,'N') = 'N'
1697: AND NVL(b.partial_adj_inv_flag,'N') = 'N'
1698: AND b.trx_id in (select c.trx_id
1699: from je_it_trx_lines_all c
1700: where c.request_id = g_conc_request_id
1701: and c.contract_identification is null)
1702: AND b.record_type in(1,2,3)
1703: GROUP BY b.application_id,
1713: AND NVL(a.partial_adj_inv_flag,'N') = 'N'
1714: AND a.record_type in (1,2,3);
1715:
1716:
1717: UPDATE je_it_trx_lines_all a -- FOR Contractor grouping.
1718: SET a.is_above_threshold = 'Y'
1719: WHERE a.request_id = g_conc_request_id
1720: AND (a.application_id,a.party_id,a.contract_identification)
1721: IN (SELECT b.application_id,
1720: AND (a.application_id,a.party_id,a.contract_identification)
1721: IN (SELECT b.application_id,
1722: b.party_id,
1723: b.contract_identification
1724: FROM je_it_trx_lines_all b
1725: WHERE b.request_id = g_conc_request_id
1726: AND b.below_threshold_flag = 'N'
1727: AND NVL(b.report_exclusion_flag,'N') = 'N'
1728: AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
1746: AND a.contract_identification IS NOT NULL
1747: AND a.record_type in (1,2,3);
1748:
1749:
1750: UPDATE je_it_trx_lines_all trx_lines -- if below_threshold flag is 'Y' then that lines has to be reported without checking against threshold
1751: SET is_above_threshold = 'Y'
1752: WHERE trx_lines.request_id = g_conc_request_id
1753: AND NVL(trx_lines.below_threshold_flag,'N') = 'Y'
1754: AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
1760: --record type 4,5 contains only the variable notes which are not applied
1761: --and variation notes applied but the original invoices declared in previous declaration year
1762: --variation notes with above two scenarios has to be reported. so update is_threshold_flag to 'Y'
1763:
1764: UPDATE je_it_trx_lines_all trx_lines
1765: SET is_above_threshold = 'Y'
1766: WHERE trx_lines.request_id = g_conc_request_id
1767: AND trx_lines.record_type in (4,5)
1768: AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
1771: AND NVL(trx_lines.orig_trx_missing_flag,'N') = 'N'
1772: AND NVL(trx_lines.partial_adj_inv_flag,'N') = 'N'
1773: AND (trx_lines.orig_trx_id IS NULL
1774: OR NOT EXISTS (SELECT 1 --to filter credit memo lines already reported.
1775: FROM je_it_trx_lines_all a,
1776: je_it_above_thr_hdr_all b
1777: WHERE a.request_id = b.request_id
1778: AND b.year_of_declaration in (g_year-1,g_year-2)
1779: AND a.trx_id = trx_lines.trx_id
1906: MAX(b.indv_party_city) indv_party_city ,
1907: MAX(b.indv_party_province) indv_party_province ,
1908: MAX(b.company_city) company_city ,
1909: MAX(b.company_address) company_address
1910: FROM je_it_trx_lines_all b
1911: WHERE b.request_id = g_conc_request_id
1912: AND NVL(b.is_above_threshold, 'N') = 'Y'
1913: AND b.record_type IN(1, 2, 3)
1914: AND EXISTS
1914: AND EXISTS
1915: (SELECT
1916: /*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
1917: 'exists'
1918: FROM je_it_trx_lines_all a
1919: WHERE a.request_id = g_conc_request_id
1920: AND a.trx_id = b.trx_id
1921: AND a.record_type IN(1, 2, 3)
1922: AND a.contract_identification IS NULL
1954: MAX(b.indv_party_city) indv_party_city ,
1955: MAX(b.indv_party_province) indv_party_province ,
1956: MAX(b.company_city) company_city ,
1957: MAX(b.company_address) company_address
1958: FROM je_it_trx_lines_all b
1959: WHERE b.request_id = g_conc_request_id
1960: AND b.contract_identification IS NOT NULL
1961: AND NVL(b.is_above_threshold, 'N') = 'Y'
1962: AND b.record_type IN(1, 2, 3)
1963: AND NOT EXISTS
1964: (SELECT
1965: /*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
1966: 'exists'
1967: FROM je_it_trx_lines_all a
1968: WHERE a.request_id = g_conc_request_id
1969: AND a.trx_id = b.trx_id
1970: AND a.record_type IN(1, 2, 3)
1971: AND a.contract_identification IS NULL
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
2003: AND trx_lines.party_id = trx.party_id
2004: AND trx_lines.application_id = trx.application_id
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
2029: AND trx_lines.party_id = trx.party_id
2030: AND trx_lines.application_id = trx.application_id
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)
2059: FROM je_it_trx_lines_all b
2060: WHERE request_id = g_conc_request_id
2061: AND contract_identification IS NULL
2062: AND b.trx_id = a.trx_id
2063: AND b.application_id=a.application_id )
2212: MAX(indv_party_province) indv_party_province,
2213: MAX(company_city) company_city,
2214: MAX(company_address) company_address,
2215: orig_trx_id
2216: FROM je_it_trx_lines_all
2217: WHERE request_id = g_conc_request_id
2218: AND record_type in (4,5)
2219: AND NVL(is_above_threshold,'N') = 'Y'
2220: GROUP BY application_id,
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)
2256: FROM je_it_trx_lines_all trx_lines,
2257: je_it_above_thr_hdr_all hdr
2258: WHERE trx_lines.request_id = hdr.request_id
2259: AND year_of_declaration in (g_year,g_year-1,g_year-2)
2260: AND trx_lines.application_id = trx.application_id