[Home] [Help]
325:
326: xProgress := '2000-10';
327: FOR k IN 1..tbl.count
328: LOOP
329: dbms_sql.define_column ( c,
330: k,
331: col,
332: col_size );
333: END LOOP;
1251:
1252: END LOOP;
1253:
1254: xProgress := '2060-50';
1255: IF ( dbms_sql.last_row_count = 0 )
1256: THEN
1257: ec_debug.pl (1,
1258: 'EC',
1259: 'ECE_NO_ROW_SELECTED',
1628: :transaction_id is a place holder for foreign key value.
1629: A PL/SQL table (list of values) will be used to store data.
1630: Procedure ece_flatfile.Find_pos will be used to locate the specific
1631: data value in the PL/SQL table.
1632: dbms_sql (Native Oracle db functions that come with every Oracle Apps)
1633: dbms_sql.bind_variable will be used to assign data value to :transaction_id.
1634:
1635: Let's use the above example:
1636:
1629: A PL/SQL table (list of values) will be used to store data.
1630: Procedure ece_flatfile.Find_pos will be used to locate the specific
1631: data value in the PL/SQL table.
1632: dbms_sql (Native Oracle db functions that come with every Oracle Apps)
1633: dbms_sql.bind_variable will be used to assign data value to :transaction_id.
1634:
1635: Let's use the above example:
1636:
1637: 1. Execute dynamic SQL 1 for headers (A) data
1902: *** Open a cursor for each of the SELECT call
1903: ***/
1904:
1905: xProgress := '2212-60';
1906: Header_sel_c := dbms_sql.open_cursor;
1907:
1908: xProgress := '2213-60';
1909: Header_1_sel_c := dbms_sql.open_cursor;
1910:
1905: xProgress := '2212-60';
1906: Header_sel_c := dbms_sql.open_cursor;
1907:
1908: xProgress := '2213-60';
1909: Header_1_sel_c := dbms_sql.open_cursor;
1910:
1911: xProgress := '2214-60';
1912: Alw_chg_h_sel_c := dbms_sql.open_cursor;
1913:
1908: xProgress := '2213-60';
1909: Header_1_sel_c := dbms_sql.open_cursor;
1910:
1911: xProgress := '2214-60';
1912: Alw_chg_h_sel_c := dbms_sql.open_cursor;
1913:
1914: xProgress := '2215-60';
1915: Line_sel_c := dbms_sql.open_cursor;
1916:
1911: xProgress := '2214-60';
1912: Alw_chg_h_sel_c := dbms_sql.open_cursor;
1913:
1914: xProgress := '2215-60';
1915: Line_sel_c := dbms_sql.open_cursor;
1916:
1917: xProgress := '2216-60';
1918: Line_t_sel_c := dbms_sql.open_cursor;
1919:
1914: xProgress := '2215-60';
1915: Line_sel_c := dbms_sql.open_cursor;
1916:
1917: xProgress := '2216-60';
1918: Line_t_sel_c := dbms_sql.open_cursor;
1919:
1920: xProgress := '2217-60';
1921: Alw_chg_l_sel_c := dbms_sql.open_cursor;
1922:
1917: xProgress := '2216-60';
1918: Line_t_sel_c := dbms_sql.open_cursor;
1919:
1920: xProgress := '2217-60';
1921: Alw_chg_l_sel_c := dbms_sql.open_cursor;
1922:
1923: xProgress := '2218-60';
1924: Header_del_c1 := dbms_sql.open_cursor;
1925:
1920: xProgress := '2217-60';
1921: Alw_chg_l_sel_c := dbms_sql.open_cursor;
1922:
1923: xProgress := '2218-60';
1924: Header_del_c1 := dbms_sql.open_cursor;
1925:
1926: xProgress := '2219-60';
1927: Header_1_del_c1 := dbms_sql.open_cursor;
1928:
1923: xProgress := '2218-60';
1924: Header_del_c1 := dbms_sql.open_cursor;
1925:
1926: xProgress := '2219-60';
1927: Header_1_del_c1 := dbms_sql.open_cursor;
1928:
1929: xProgress := '2220-60';
1930: Alw_chg_h_del_c1 := dbms_sql.open_cursor;
1931:
1926: xProgress := '2219-60';
1927: Header_1_del_c1 := dbms_sql.open_cursor;
1928:
1929: xProgress := '2220-60';
1930: Alw_chg_h_del_c1 := dbms_sql.open_cursor;
1931:
1932: xProgress := '2221-60';
1933: Line_del_c1 := dbms_sql.open_cursor;
1934:
1929: xProgress := '2220-60';
1930: Alw_chg_h_del_c1 := dbms_sql.open_cursor;
1931:
1932: xProgress := '2221-60';
1933: Line_del_c1 := dbms_sql.open_cursor;
1934:
1935: xProgress := '2222-60';
1936: Line_t_del_c1 := dbms_sql.open_cursor;
1937:
1932: xProgress := '2221-60';
1933: Line_del_c1 := dbms_sql.open_cursor;
1934:
1935: xProgress := '2222-60';
1936: Line_t_del_c1 := dbms_sql.open_cursor;
1937:
1938: xProgress := '2223-60';
1939: Alw_chg_l_del_c1 := dbms_sql.open_cursor;
1940:
1935: xProgress := '2222-60';
1936: Line_t_del_c1 := dbms_sql.open_cursor;
1937:
1938: xProgress := '2223-60';
1939: Alw_chg_l_del_c1 := dbms_sql.open_cursor;
1940:
1941: xProgress := '2224-60';
1942: Header_del_c2 := dbms_sql.open_cursor;
1943:
1938: xProgress := '2223-60';
1939: Alw_chg_l_del_c1 := dbms_sql.open_cursor;
1940:
1941: xProgress := '2224-60';
1942: Header_del_c2 := dbms_sql.open_cursor;
1943:
1944: xProgress := '2225-60';
1945: Header_1_del_c2 := dbms_sql.open_cursor;
1946:
1941: xProgress := '2224-60';
1942: Header_del_c2 := dbms_sql.open_cursor;
1943:
1944: xProgress := '2225-60';
1945: Header_1_del_c2 := dbms_sql.open_cursor;
1946:
1947: xProgress := '2226-60';
1948: Alw_chg_h_del_c2 := dbms_sql.open_cursor;
1949:
1944: xProgress := '2225-60';
1945: Header_1_del_c2 := dbms_sql.open_cursor;
1946:
1947: xProgress := '2226-60';
1948: Alw_chg_h_del_c2 := dbms_sql.open_cursor;
1949:
1950: xProgress := '2227-60';
1951: Line_del_c2 := dbms_sql.open_cursor;
1952:
1947: xProgress := '2226-60';
1948: Alw_chg_h_del_c2 := dbms_sql.open_cursor;
1949:
1950: xProgress := '2227-60';
1951: Line_del_c2 := dbms_sql.open_cursor;
1952:
1953: xProgress := '2228-60';
1954: Line_t_del_c2 := dbms_sql.open_cursor;
1955:
1950: xProgress := '2227-60';
1951: Line_del_c2 := dbms_sql.open_cursor;
1952:
1953: xProgress := '2228-60';
1954: Line_t_del_c2 := dbms_sql.open_cursor;
1955:
1956: xProgress := '2229-60';
1957: Alw_chg_l_del_c2 := dbms_sql.open_cursor;
1958:
1953: xProgress := '2228-60';
1954: Line_t_del_c2 := dbms_sql.open_cursor;
1955:
1956: xProgress := '2229-60';
1957: Alw_chg_l_del_c2 := dbms_sql.open_cursor;
1958:
1959: /***
1960: *** Parse each of the SELECT and DELETE statement
1961: ***/
1961: ***/
1962:
1963: xProgress := '2230-60';
1964: BEGIN
1965: dbms_sql.parse ( Header_sel_c,
1966: cHeader_select,
1967: dbms_sql.native );
1968: EXCEPTION
1969: WHEN OTHERS THEN
1963: xProgress := '2230-60';
1964: BEGIN
1965: dbms_sql.parse ( Header_sel_c,
1966: cHeader_select,
1967: dbms_sql.native );
1968: EXCEPTION
1969: WHEN OTHERS THEN
1970: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1971: cHeader_select);
1966: cHeader_select,
1967: dbms_sql.native );
1968: EXCEPTION
1969: WHEN OTHERS THEN
1970: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1971: cHeader_select);
1972: app_exception.raise_exception;
1973: END;
1974:
1973: END;
1974:
1975: xProgress := '2231-60';
1976: BEGIN
1977: dbms_sql.parse ( Header_1_sel_c,
1978: cHeader_1_select,
1979: dbms_sql.native );
1980: EXCEPTION
1981: WHEN OTHERS THEN
1975: xProgress := '2231-60';
1976: BEGIN
1977: dbms_sql.parse ( Header_1_sel_c,
1978: cHeader_1_select,
1979: dbms_sql.native );
1980: EXCEPTION
1981: WHEN OTHERS THEN
1982: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1983: cHeader_1_select);
1978: cHeader_1_select,
1979: dbms_sql.native );
1980: EXCEPTION
1981: WHEN OTHERS THEN
1982: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1983: cHeader_1_select);
1984: app_exception.raise_exception;
1985: END;
1986:
1985: END;
1986:
1987: xProgress := '2232-60';
1988: BEGIN
1989: dbms_sql.parse ( Alw_chg_h_sel_c,
1990: cAlw_chg_h_select,
1991: dbms_sql.native );
1992: EXCEPTION
1993: WHEN OTHERS THEN
1987: xProgress := '2232-60';
1988: BEGIN
1989: dbms_sql.parse ( Alw_chg_h_sel_c,
1990: cAlw_chg_h_select,
1991: dbms_sql.native );
1992: EXCEPTION
1993: WHEN OTHERS THEN
1994: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1995: cAlw_chg_h_select );
1990: cAlw_chg_h_select,
1991: dbms_sql.native );
1992: EXCEPTION
1993: WHEN OTHERS THEN
1994: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1995: cAlw_chg_h_select );
1996: app_exception.raise_exception;
1997: END;
1998:
1997: END;
1998:
1999: xProgress := '2233-60';
2000: BEGIN
2001: dbms_sql.parse ( Line_sel_c,
2002: cLine_select,
2003: dbms_sql.native );
2004: EXCEPTION
2005: WHEN OTHERS THEN
1999: xProgress := '2233-60';
2000: BEGIN
2001: dbms_sql.parse ( Line_sel_c,
2002: cLine_select,
2003: dbms_sql.native );
2004: EXCEPTION
2005: WHEN OTHERS THEN
2006: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2007: cLine_select );
2002: cLine_select,
2003: dbms_sql.native );
2004: EXCEPTION
2005: WHEN OTHERS THEN
2006: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2007: cLine_select );
2008: app_exception.raise_exception;
2009: END;
2010:
2009: END;
2010:
2011: xProgress := '2234-60';
2012: BEGIN
2013: dbms_sql.parse ( Line_t_sel_c,
2014: cLine_t_select,
2015: dbms_sql.native );
2016: EXCEPTION
2017: WHEN OTHERS THEN
2011: xProgress := '2234-60';
2012: BEGIN
2013: dbms_sql.parse ( Line_t_sel_c,
2014: cLine_t_select,
2015: dbms_sql.native );
2016: EXCEPTION
2017: WHEN OTHERS THEN
2018: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2019: cLine_t_select );
2014: cLine_t_select,
2015: dbms_sql.native );
2016: EXCEPTION
2017: WHEN OTHERS THEN
2018: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2019: cLine_t_select );
2020: app_exception.raise_exception;
2021: END;
2022:
2021: END;
2022:
2023: xProgress := '2235-60';
2024: BEGIN
2025: dbms_sql.parse ( Alw_chg_l_sel_c,
2026: cAlw_chg_l_select,
2027: dbms_sql.native );
2028: EXCEPTION
2029: WHEN OTHERS THEN
2023: xProgress := '2235-60';
2024: BEGIN
2025: dbms_sql.parse ( Alw_chg_l_sel_c,
2026: cAlw_chg_l_select,
2027: dbms_sql.native );
2028: EXCEPTION
2029: WHEN OTHERS THEN
2030: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2031: cAlw_chg_l_select );
2026: cAlw_chg_l_select,
2027: dbms_sql.native );
2028: EXCEPTION
2029: WHEN OTHERS THEN
2030: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2031: cAlw_chg_l_select );
2032: app_exception.raise_exception;
2033: END;
2034:
2033: END;
2034:
2035: xProgress := '2236-60';
2036: BEGIN
2037: dbms_sql.parse ( Header_del_c1,
2038: cHeader_delete1,
2039: dbms_sql.native );
2040: EXCEPTION
2041: WHEN OTHERS THEN
2035: xProgress := '2236-60';
2036: BEGIN
2037: dbms_sql.parse ( Header_del_c1,
2038: cHeader_delete1,
2039: dbms_sql.native );
2040: EXCEPTION
2041: WHEN OTHERS THEN
2042: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2043: cHeader_delete1 );
2038: cHeader_delete1,
2039: dbms_sql.native );
2040: EXCEPTION
2041: WHEN OTHERS THEN
2042: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2043: cHeader_delete1 );
2044: app_exception.raise_exception;
2045: END;
2046:
2045: END;
2046:
2047: xProgress := '2237-60';
2048: BEGIN
2049: dbms_sql.parse ( Header_1_del_c1,
2050: cHeader_1_delete1,
2051: dbms_sql.native );
2052: EXCEPTION
2053: WHEN OTHERS THEN
2047: xProgress := '2237-60';
2048: BEGIN
2049: dbms_sql.parse ( Header_1_del_c1,
2050: cHeader_1_delete1,
2051: dbms_sql.native );
2052: EXCEPTION
2053: WHEN OTHERS THEN
2054: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2055: cHeader_1_delete1 );
2050: cHeader_1_delete1,
2051: dbms_sql.native );
2052: EXCEPTION
2053: WHEN OTHERS THEN
2054: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2055: cHeader_1_delete1 );
2056: app_exception.raise_exception;
2057: END;
2058:
2057: END;
2058:
2059: xProgress := '2238-60';
2060: BEGIN
2061: dbms_sql.parse ( Alw_chg_h_del_c1,
2062: cAlw_chg_h_delete1,
2063: dbms_sql.native );
2064: EXCEPTION
2065: WHEN OTHERS THEN
2059: xProgress := '2238-60';
2060: BEGIN
2061: dbms_sql.parse ( Alw_chg_h_del_c1,
2062: cAlw_chg_h_delete1,
2063: dbms_sql.native );
2064: EXCEPTION
2065: WHEN OTHERS THEN
2066: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2067: cAlw_chg_h_delete1 );
2062: cAlw_chg_h_delete1,
2063: dbms_sql.native );
2064: EXCEPTION
2065: WHEN OTHERS THEN
2066: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2067: cAlw_chg_h_delete1 );
2068: app_exception.raise_exception;
2069: END;
2070:
2069: END;
2070:
2071: xProgress := '2239-60';
2072: BEGIN
2073: dbms_sql.parse ( Line_del_c1,
2074: cLine_delete1,
2075: dbms_sql.native );
2076: EXCEPTION
2077: WHEN OTHERS THEN
2071: xProgress := '2239-60';
2072: BEGIN
2073: dbms_sql.parse ( Line_del_c1,
2074: cLine_delete1,
2075: dbms_sql.native );
2076: EXCEPTION
2077: WHEN OTHERS THEN
2078: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2079: cLine_delete1 );
2074: cLine_delete1,
2075: dbms_sql.native );
2076: EXCEPTION
2077: WHEN OTHERS THEN
2078: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2079: cLine_delete1 );
2080: app_exception.raise_exception;
2081: END;
2082:
2081: END;
2082:
2083: xProgress := '2240-60';
2084: BEGIN
2085: dbms_sql.parse ( Line_t_del_c1,
2086: cLine_t_delete1,
2087: dbms_sql.native );
2088: EXCEPTION
2089: WHEN OTHERS THEN
2083: xProgress := '2240-60';
2084: BEGIN
2085: dbms_sql.parse ( Line_t_del_c1,
2086: cLine_t_delete1,
2087: dbms_sql.native );
2088: EXCEPTION
2089: WHEN OTHERS THEN
2090: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2091: cLine_t_delete1 );
2086: cLine_t_delete1,
2087: dbms_sql.native );
2088: EXCEPTION
2089: WHEN OTHERS THEN
2090: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2091: cLine_t_delete1 );
2092: app_exception.raise_exception;
2093: END;
2094:
2093: END;
2094:
2095: xProgress := '2241-60';
2096: BEGIN
2097: dbms_sql.parse ( Alw_chg_l_del_c1,
2098: cAlw_chg_l_delete1,
2099: dbms_sql.native );
2100: EXCEPTION
2101: WHEN OTHERS THEN
2095: xProgress := '2241-60';
2096: BEGIN
2097: dbms_sql.parse ( Alw_chg_l_del_c1,
2098: cAlw_chg_l_delete1,
2099: dbms_sql.native );
2100: EXCEPTION
2101: WHEN OTHERS THEN
2102: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2103: cAlw_chg_l_delete1 );
2098: cAlw_chg_l_delete1,
2099: dbms_sql.native );
2100: EXCEPTION
2101: WHEN OTHERS THEN
2102: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2103: cAlw_chg_l_delete1 );
2104: app_exception.raise_exception;
2105: END;
2106:
2105: END;
2106:
2107: xProgress := '2242-60';
2108: BEGIN
2109: dbms_sql.parse ( Header_del_c2,
2110: cHeader_delete2,
2111: dbms_sql.native );
2112: EXCEPTION
2113: WHEN OTHERS THEN
2107: xProgress := '2242-60';
2108: BEGIN
2109: dbms_sql.parse ( Header_del_c2,
2110: cHeader_delete2,
2111: dbms_sql.native );
2112: EXCEPTION
2113: WHEN OTHERS THEN
2114: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2115: cHeader_delete2 );
2110: cHeader_delete2,
2111: dbms_sql.native );
2112: EXCEPTION
2113: WHEN OTHERS THEN
2114: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2115: cHeader_delete2 );
2116: app_exception.raise_exception;
2117: END;
2118:
2117: END;
2118:
2119: xProgress := '2243-60';
2120: BEGIN
2121: dbms_sql.parse ( Header_1_del_c2,
2122: cHeader_1_delete2,
2123: dbms_sql.native );
2124: EXCEPTION
2125: WHEN OTHERS THEN
2119: xProgress := '2243-60';
2120: BEGIN
2121: dbms_sql.parse ( Header_1_del_c2,
2122: cHeader_1_delete2,
2123: dbms_sql.native );
2124: EXCEPTION
2125: WHEN OTHERS THEN
2126: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2127: cHeader_1_delete2 );
2122: cHeader_1_delete2,
2123: dbms_sql.native );
2124: EXCEPTION
2125: WHEN OTHERS THEN
2126: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2127: cHeader_1_delete2 );
2128: app_exception.raise_exception;
2129: END;
2130:
2129: END;
2130:
2131: xProgress := '2244-60';
2132: BEGIN
2133: dbms_sql.parse ( Alw_chg_h_del_c2,
2134: cAlw_chg_h_delete2,
2135: dbms_sql.native );
2136: EXCEPTION
2137: WHEN OTHERS THEN
2131: xProgress := '2244-60';
2132: BEGIN
2133: dbms_sql.parse ( Alw_chg_h_del_c2,
2134: cAlw_chg_h_delete2,
2135: dbms_sql.native );
2136: EXCEPTION
2137: WHEN OTHERS THEN
2138: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2139: cAlw_chg_h_delete2 );
2134: cAlw_chg_h_delete2,
2135: dbms_sql.native );
2136: EXCEPTION
2137: WHEN OTHERS THEN
2138: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2139: cAlw_chg_h_delete2 );
2140: app_exception.raise_exception;
2141: END;
2142:
2141: END;
2142:
2143: xProgress := '2245-60';
2144: BEGIN
2145: dbms_sql.parse ( Line_del_c2,
2146: cLine_delete2,
2147: dbms_sql.native );
2148: EXCEPTION
2149: WHEN OTHERS THEN
2143: xProgress := '2245-60';
2144: BEGIN
2145: dbms_sql.parse ( Line_del_c2,
2146: cLine_delete2,
2147: dbms_sql.native );
2148: EXCEPTION
2149: WHEN OTHERS THEN
2150: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2151: cLine_delete2 );
2146: cLine_delete2,
2147: dbms_sql.native );
2148: EXCEPTION
2149: WHEN OTHERS THEN
2150: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2151: cLine_delete2 );
2152: app_exception.raise_exception;
2153: END;
2154:
2153: END;
2154:
2155: xProgress := '2246-60';
2156: BEGIN
2157: dbms_sql.parse ( Line_t_del_c2,
2158: cLine_t_delete2,
2159: dbms_sql.native );
2160: EXCEPTION
2161: WHEN OTHERS THEN
2155: xProgress := '2246-60';
2156: BEGIN
2157: dbms_sql.parse ( Line_t_del_c2,
2158: cLine_t_delete2,
2159: dbms_sql.native );
2160: EXCEPTION
2161: WHEN OTHERS THEN
2162: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2163: cLine_t_delete2 );
2158: cLine_t_delete2,
2159: dbms_sql.native );
2160: EXCEPTION
2161: WHEN OTHERS THEN
2162: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2163: cLine_t_delete2 );
2164: app_exception.raise_exception;
2165: END;
2166:
2165: END;
2166:
2167: xProgress := '2247-60';
2168: BEGIN
2169: dbms_sql.parse ( Alw_chg_l_del_c2,
2170: cAlw_chg_l_delete2,
2171: dbms_sql.native );
2172: EXCEPTION
2173: WHEN OTHERS THEN
2167: xProgress := '2247-60';
2168: BEGIN
2169: dbms_sql.parse ( Alw_chg_l_del_c2,
2170: cAlw_chg_l_delete2,
2171: dbms_sql.native );
2172: EXCEPTION
2173: WHEN OTHERS THEN
2174: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2175: cAlw_chg_l_delete2 );
2170: cAlw_chg_l_delete2,
2171: dbms_sql.native );
2172: EXCEPTION
2173: WHEN OTHERS THEN
2174: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2175: cAlw_chg_l_delete2 );
2176: app_exception.raise_exception;
2177: END;
2178:
2212:
2213: xProgress := '2270-60';
2214: FOR k IN 1..iHeader_count
2215: LOOP
2216: dbms_sql.define_column ( Header_sel_c,
2217: k,
2218: cHeader_select,
2219: ece_flatfile_pvt.G_MaxColWidth );
2220: END LOOP;
2223: *** Need rowid for delete (Header Level)
2224: ***/
2225:
2226: xProgress := '2280-60';
2227: dbms_sql.define_column_rowid ( Header_sel_c,
2228: iHeader_count + 1,
2229: rHeader_rowid );
2230:
2231: xProgress := '2282-60';
2228: iHeader_count + 1,
2229: rHeader_rowid );
2230:
2231: xProgress := '2282-60';
2232: dbms_sql.define_column_rowid ( Header_sel_c,
2233: iHeader_count + 2,
2234: rHeader_X_rowid );
2235:
2236: xProgress := '2284-60';
2233: iHeader_count + 2,
2234: rHeader_X_rowid );
2235:
2236: xProgress := '2284-60';
2237: dbms_sql.define_column ( Header_sel_c,
2238: iHeader_count + 3,
2239: nTrans_id );
2240:
2241:
2241:
2242: xProgress := '2290-60';
2243: FOR k IN 1..iHeader_1_count
2244: LOOP
2245: dbms_sql.define_column ( Header_1_sel_c,
2246: k,
2247: cHeader_1_select,
2248: ece_flatfile_pvt.G_MaxColWidth );
2249: END LOOP;
2252: *** Need rowid for delete (Header 1 Level)
2253: ***/
2254:
2255: xProgress := '2292-60';
2256: dbms_sql.define_column_rowid ( Header_1_sel_c,
2257: iHeader_1_count + 1,
2258: rHeader_1_rowid );
2259:
2260: xProgress := '2294-60';
2257: iHeader_1_count + 1,
2258: rHeader_1_rowid );
2259:
2260: xProgress := '2294-60';
2261: dbms_sql.define_column_rowid ( Header_1_sel_c,
2262: iHeader_1_count + 2,
2263: rHeader_1_X_rowid );
2264:
2265: xProgress := '2300-60';
2264:
2265: xProgress := '2300-60';
2266: FOR k IN 1..iAlw_chg_h_count
2267: LOOP
2268: dbms_sql.define_column ( Alw_chg_h_sel_c,
2269: k,
2270: cAlw_chg_h_select,
2271: ece_flatfile_pvt.G_MaxColWidth );
2272: END LOOP;
2275: *** Need rowid for delete (Allowance Charges Header Level)
2276: ***/
2277:
2278: xProgress := '2310-60';
2279: dbms_sql.define_column_rowid ( Alw_chg_h_sel_c,
2280: iAlw_chg_h_count + 1,
2281: rAlw_chg_h_rowid );
2282:
2283: xProgress := '2312-60';
2280: iAlw_chg_h_count + 1,
2281: rAlw_chg_h_rowid );
2282:
2283: xProgress := '2312-60';
2284: dbms_sql.define_column_rowid ( Alw_chg_h_sel_c,
2285: iAlw_chg_h_count + 2,
2286: rAlw_chg_X_rowid );
2287:
2288: xProgress := '2320-60';
2287:
2288: xProgress := '2320-60';
2289: FOR k IN 1..iLine_count
2290: LOOP
2291: dbms_sql.define_column ( Line_sel_c,
2292: k,
2293: cLine_select,
2294: ece_flatfile_pvt.G_MaxColWidth );
2295: END LOOP;
2298: *** Need rowid for delete (Line Level)
2299: ***/
2300:
2301: xProgress := '2330-60';
2302: dbms_sql.define_column_rowid ( Line_sel_c,
2303: iLine_count + 1,
2304: rLine_rowid );
2305:
2306: xProgress := '2332-60';
2303: iLine_count + 1,
2304: rLine_rowid );
2305:
2306: xProgress := '2332-60';
2307: dbms_sql.define_column_rowid ( Line_sel_c,
2308: iLine_count + 2,
2309: rLine_X_rowid );
2310:
2311: xProgress := '2340-60';
2310:
2311: xProgress := '2340-60';
2312: FOR k IN 1..iLine_t_count
2313: LOOP
2314: dbms_sql.define_column ( Line_t_sel_c,
2315: k,
2316: cLine_t_select,
2317: ece_flatfile_pvt.G_MaxColWidth );
2318: END LOOP;
2321: *** Need rowid for delete (Line Level)
2322: ***/
2323:
2324: xProgress := '2350-60';
2325: dbms_sql.define_column_rowid ( Line_t_sel_c,
2326: iLine_t_count + 1,
2327: rLine_t_rowid );
2328:
2329: xProgress := '2352-60';
2326: iLine_t_count + 1,
2327: rLine_t_rowid );
2328:
2329: xProgress := '2352-60';
2330: dbms_sql.define_column_rowid ( Line_t_sel_c,
2331: iLine_t_count + 2,
2332: rLine_t_X_rowid );
2333:
2334: xProgress := '2360-60';
2333:
2334: xProgress := '2360-60';
2335: FOR k IN 1..iAlw_chg_l_count
2336: LOOP
2337: dbms_sql.define_column ( Alw_chg_l_sel_c,
2338: k,
2339: cAlw_chg_l_select,
2340: ece_flatfile_pvt.G_MaxColWidth );
2341: END LOOP;
2344: *** Need rowid for delete (Allowance Charges Detail Level)
2345: ***/
2346:
2347: xProgress := '2370-60';
2348: dbms_sql.define_column_rowid ( Alw_chg_l_sel_c,
2349: iAlw_chg_l_count + 1,
2350: rAlw_chg_l_rowid );
2351:
2352: xProgress := '2372-60';
2349: iAlw_chg_l_count + 1,
2350: rAlw_chg_l_rowid );
2351:
2352: xProgress := '2372-60';
2353: dbms_sql.define_column_rowid ( Alw_chg_l_sel_c,
2354: iAlw_chg_l_count + 2,
2355: rAlw_chg_X_rowid );
2356:
2357: /**************************************************************
2371: ec_debug.pl ( 3, 'nPos1: ',nPos1 );
2372:
2373: -- EXECUTE the SELECT statement
2374:
2375: dbms_sql.bind_variable ( Header_sel_c, 'b1', iRun_id );
2376:
2377: xProgress := '2390-60';
2378: dummy := dbms_sql.execute ( Header_sel_c );
2379:
2374:
2375: dbms_sql.bind_variable ( Header_sel_c, 'b1', iRun_id );
2376:
2377: xProgress := '2390-60';
2378: dummy := dbms_sql.execute ( Header_sel_c );
2379:
2380: /*** --------------------------------------------------------------
2381: *** With data for each HEADER line, populate the ECE_OUTPUT table
2382: *** then populate ECE_OUTPUT with data from all HEADER 1 that belongs
2389: -- HEADER - HEADER 1 - HEADER ALLOWANCES and CHARGES - LINE - LINE ALLOWANCES and CHARGES
2390: -- - LINE TAX ...
2391:
2392: xProgress := '2400-60';
2393: WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
2394: LOOP -- Header
2395:
2396: /***
2397: *** store values in pl/sql table
2399:
2400: xProgress := '2410-60';
2401: FOR i IN 1..iHeader_count
2402: LOOP
2403: dbms_sql.column_value ( Header_sel_c,
2404: i,
2405: l_Header_tbl(i).value );
2406: END LOOP;
2407:
2405: l_Header_tbl(i).value );
2406: END LOOP;
2407:
2408: xProgress := '2420-60';
2409: dbms_sql.column_value ( Header_sel_c,
2410: iHeader_count + 1,
2411: rHeader_rowid );
2412:
2413: xProgress := '2422-60';
2410: iHeader_count + 1,
2411: rHeader_rowid );
2412:
2413: xProgress := '2422-60';
2414: dbms_sql.column_value ( Header_sel_c,
2415: iHeader_count + 2,
2416: rHeader_X_rowid );
2417:
2418: xProgress := '2424-60';
2415: iHeader_count + 2,
2416: rHeader_X_rowid );
2417:
2418: xProgress := '2424-60';
2419: dbms_sql.column_value (Header_sel_c,
2420: iHeader_count + 3,
2421: nTrans_id );
2422:
2423: xProgress := '2430-60';
2465:
2466: /*** -- set values into binding variables
2467: ***/
2468: xProgress := '2452-60';
2469: dbms_sql.bind_variable ( Header_1_sel_c, 'transaction_id', nTrans_id );
2470:
2471: dbms_sql.bind_variable ( Header_1_sel_c, 'b2', iRun_id );
2472:
2473: xProgress := '2454-60';
2467: ***/
2468: xProgress := '2452-60';
2469: dbms_sql.bind_variable ( Header_1_sel_c, 'transaction_id', nTrans_id );
2470:
2471: dbms_sql.bind_variable ( Header_1_sel_c, 'b2', iRun_id );
2472:
2473: xProgress := '2454-60';
2474: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'transaction_id', nTrans_id );
2475:
2470:
2471: dbms_sql.bind_variable ( Header_1_sel_c, 'b2', iRun_id );
2472:
2473: xProgress := '2454-60';
2474: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'transaction_id', nTrans_id );
2475:
2476: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'b3', iRun_id );
2477:
2478: xProgress := '2456-60';
2472:
2473: xProgress := '2454-60';
2474: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'transaction_id', nTrans_id );
2475:
2476: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'b3', iRun_id );
2477:
2478: xProgress := '2456-60';
2479: dbms_sql.bind_variable ( Line_sel_c, 'transaction_id', nTrans_id );
2480:
2475:
2476: dbms_sql.bind_variable ( Alw_chg_h_sel_c, 'b3', iRun_id );
2477:
2478: xProgress := '2456-60';
2479: dbms_sql.bind_variable ( Line_sel_c, 'transaction_id', nTrans_id );
2480:
2481: dbms_sql.bind_variable ( Line_sel_c, 'b4', iRun_id );
2482:
2483: xProgress := '2458-60';
2477:
2478: xProgress := '2456-60';
2479: dbms_sql.bind_variable ( Line_sel_c, 'transaction_id', nTrans_id );
2480:
2481: dbms_sql.bind_variable ( Line_sel_c, 'b4', iRun_id );
2482:
2483: xProgress := '2458-60';
2484: dbms_sql.bind_variable ( Line_t_sel_c, 'transaction_id', nTrans_id );
2485:
2480:
2481: dbms_sql.bind_variable ( Line_sel_c, 'b4', iRun_id );
2482:
2483: xProgress := '2458-60';
2484: dbms_sql.bind_variable ( Line_t_sel_c, 'transaction_id', nTrans_id );
2485:
2486: dbms_sql.bind_variable ( Line_t_sel_c, 'b5', iRun_id );
2487:
2488: xProgress := '2460-60';
2482:
2483: xProgress := '2458-60';
2484: dbms_sql.bind_variable ( Line_t_sel_c, 'transaction_id', nTrans_id );
2485:
2486: dbms_sql.bind_variable ( Line_t_sel_c, 'b5', iRun_id );
2487:
2488: xProgress := '2460-60';
2489: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'transaction_id', nTrans_id );
2490:
2485:
2486: dbms_sql.bind_variable ( Line_t_sel_c, 'b5', iRun_id );
2487:
2488: xProgress := '2460-60';
2489: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'transaction_id', nTrans_id );
2490:
2491: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'b6', iRun_id );
2492:
2493: xProgress := '2470-60';
2487:
2488: xProgress := '2460-60';
2489: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'transaction_id', nTrans_id );
2490:
2491: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'b6', iRun_id );
2492:
2493: xProgress := '2470-60';
2494: dummy := dbms_sql.execute ( Header_1_sel_c );
2495:
2490:
2491: dbms_sql.bind_variable ( Alw_chg_l_sel_c, 'b6', iRun_id );
2492:
2493: xProgress := '2470-60';
2494: dummy := dbms_sql.execute ( Header_1_sel_c );
2495:
2496: /*****
2497: ***** -- header 1 loop starts here
2498: *****/
2497: ***** -- header 1 loop starts here
2498: *****/
2499:
2500: xProgress := '2480-60';
2501: WHILE dbms_sql.fetch_rows ( Header_1_sel_c ) > 0
2502: LOOP --- Header 1
2503:
2504: /*****
2505: ***** store values in pl/sql table
2507:
2508: xProgress := '2490-60';
2509: FOR l IN 1..iHeader_1_count
2510: LOOP
2511: dbms_sql.column_value ( Header_1_sel_c,
2512: l,
2513: l_Header_1_tbl(l).value );
2514: END LOOP;
2515:
2513: l_Header_1_tbl(l).value );
2514: END LOOP;
2515:
2516: xProgress := '2500-60';
2517: dbms_sql.column_value ( Header_1_sel_c,
2518: iHeader_1_count + 1,
2519: rHeader_1_rowid );
2520:
2521: xProgress := '2510-60';
2518: iHeader_1_count + 1,
2519: rHeader_1_rowid );
2520:
2521: xProgress := '2510-60';
2522: dbms_sql.column_value ( Header_1_sel_c,
2523: iHeader_1_count + 2,
2524: rHeader_1_X_rowid );
2525:
2526: xProgress := '2520-60';
2531: iOutput_width,
2532: iRun_id,
2533: c_file_common_key );
2534: xProgress := '2530-60';
2535: dbms_sql.bind_variable ( Header_1_del_c1,
2536: 'col_rowid',
2537: rHeader_1_rowid );
2538:
2539: -- xProgress := '2532-60';
2536: 'col_rowid',
2537: rHeader_1_rowid );
2538:
2539: -- xProgress := '2532-60';
2540: dbms_sql.bind_variable ( Header_1_del_c2,
2541: 'col_rowid',
2542: rHeader_1_X_rowid );
2543:
2544: xProgress := '2532-60';
2541: 'col_rowid',
2542: rHeader_1_X_rowid );
2543:
2544: xProgress := '2532-60';
2545: dummy := dbms_sql.execute ( Header_1_del_c1 );
2546:
2547: xProgress := '2534-60';
2548: dummy := dbms_sql.execute ( Header_1_del_c2 );
2549:
2544: xProgress := '2532-60';
2545: dummy := dbms_sql.execute ( Header_1_del_c1 );
2546:
2547: xProgress := '2534-60';
2548: dummy := dbms_sql.execute ( Header_1_del_c2 );
2549:
2550: /* Bug 1703536 - closed the end loop to end the header 1 loop */
2551:
2552: END LOOP;
2551:
2552: END LOOP;
2553:
2554: xProgress := '2536-60';
2555: IF ( dbms_sql.last_row_count = 0 ) THEN
2556: v_LevelProcessed := 'HEADER 1';
2557: ec_debug.pl ( 1,
2558: 'EC',
2559: 'ECE_NO_DB_ROW_PROCESSED',
2566: END IF;
2567:
2568:
2569: xProgress := '2538-60';
2570: dummy := dbms_sql.execute ( Alw_chg_h_sel_c );
2571:
2572: /*****
2573: ***** -- Allowances and Charges Header loop starts here
2574: *****/
2573: ***** -- Allowances and Charges Header loop starts here
2574: *****/
2575:
2576: xProgress := '2540-60';
2577: WHILE dbms_sql.fetch_rows ( Alw_chg_h_sel_c ) > 0
2578: LOOP --- Allowances and Charges Header
2579:
2580: /*****
2581: ***** store values in pl/sql table
2583:
2584: xProgress := '2550-60';
2585: FOR m IN 1..iAlw_chg_h_count
2586: LOOP
2587: dbms_sql.column_value ( Alw_chg_h_sel_c,
2588: m,
2589: l_alw_chg_h_tbl(m).value );
2590: END LOOP;
2591:
2589: l_alw_chg_h_tbl(m).value );
2590: END LOOP;
2591:
2592: xProgress := '2560-60';
2593: dbms_sql.column_value ( Alw_chg_h_sel_c,
2594: iAlw_chg_h_count + 1,
2595: rAlw_chg_h_rowid );
2596:
2597: xProgress := '2562-60';
2594: iAlw_chg_h_count + 1,
2595: rAlw_chg_h_rowid );
2596:
2597: xProgress := '2562-60';
2598: dbms_sql.column_value ( Alw_chg_h_sel_c,
2599: iAlw_chg_h_count + 2,
2600: rAlw_chg_X_rowid );
2601:
2602: xProgress := '2570-60';
2616: **** -- Use rowid for delete
2617: ****/
2618:
2619: xProgress := '2580-60';
2620: dbms_sql.bind_variable ( Alw_chg_h_del_c1,
2621: 'col_rowid',
2622: rAlw_chg_h_rowid );
2623:
2624: xProgress := '2582-60';
2621: 'col_rowid',
2622: rAlw_chg_h_rowid );
2623:
2624: xProgress := '2582-60';
2625: dbms_sql.bind_variable ( Alw_chg_h_del_c2,
2626: 'col_rowid',
2627: rAlw_chg_X_rowid );
2628:
2629: xProgress := '2590-60';
2626: 'col_rowid',
2627: rAlw_chg_X_rowid );
2628:
2629: xProgress := '2590-60';
2630: dummy := dbms_sql.execute ( Alw_chg_h_del_c1 );
2631:
2632: xProgress := '2592-60';
2633: dummy := dbms_sql.execute ( Alw_chg_h_del_c2 );
2634:
2629: xProgress := '2590-60';
2630: dummy := dbms_sql.execute ( Alw_chg_h_del_c1 );
2631:
2632: xProgress := '2592-60';
2633: dummy := dbms_sql.execute ( Alw_chg_h_del_c2 );
2634:
2635: END LOOP;
2636:
2637: xProgress := '2594-60';
2634:
2635: END LOOP;
2636:
2637: xProgress := '2594-60';
2638: IF ( dbms_sql.last_row_count = 0 )
2639: THEN
2640: v_LevelProcessed := 'ALLOWANCE CHARGES HEADER';
2641: ec_debug.pl ( 1,
2642: 'EC',
2649: cTransaction_Type );
2650: END IF;
2651:
2652: xProgress := '2600-60';
2653: dummy := dbms_sql.execute ( Line_sel_c );
2654:
2655: /*****
2656: ***** -- line loop starts here
2657: *****/
2656: ***** -- line loop starts here
2657: *****/
2658:
2659: xProgress := '2610-60';
2660: WHILE dbms_sql.fetch_rows ( Line_sel_c ) > 0
2661: LOOP --- Line
2662:
2663: /*****
2664: ***** store values in pl/sql table
2666:
2667: xProgress := '2620-60';
2668: FOR j IN 1..iLine_count
2669: LOOP
2670: dbms_sql.column_value ( Line_sel_c,
2671: j,
2672: l_Line_tbl(j).value );
2673: END LOOP;
2674:
2672: l_Line_tbl(j).value );
2673: END LOOP;
2674:
2675: xProgress := '2630-60';
2676: dbms_sql.column_value ( Line_sel_c,
2677: iLine_count + 1,
2678: rLine_rowid );
2679:
2680: xProgress := '2632-60';
2677: iLine_count + 1,
2678: rLine_rowid );
2679:
2680: xProgress := '2632-60';
2681: dbms_sql.column_value ( Line_sel_c,
2682: iLine_count + 2,
2683: rLine_X_rowid );
2684:
2685: xProgress := '2640-60';
2716: ***** set LINE_NUMBER values
2717: *****/
2718:
2719: xProgress := '2670-60';
2720: dbms_sql.bind_variable ( Line_t_sel_c,
2721: 'line_number',
2722: l_Line_tbl(nPos1).value );
2723:
2724: xProgress := '2672-60';
2721: 'line_number',
2722: l_Line_tbl(nPos1).value );
2723:
2724: xProgress := '2672-60';
2725: dbms_sql.bind_variable ( Alw_chg_l_sel_c,
2726: 'line_number',
2727: l_Line_tbl(nPos1).value );
2728:
2729:
2727: l_Line_tbl(nPos1).value );
2728:
2729:
2730: xProgress := '2680-60';
2731: dummy := dbms_sql.execute ( Line_t_sel_c );
2732:
2733: /*****
2734: ***** -- line tax loop starts here
2735: *****/
2734: ***** -- line tax loop starts here
2735: *****/
2736:
2737: xProgress := '2690-60';
2738: WHILE dbms_sql.fetch_rows ( Line_t_sel_c ) > 0
2739: LOOP --- Line Tax
2740:
2741: /*****
2742: ***** store values in pl/sql table
2743: *****/
2744:
2745: xProgress := '2700-60';
2746: FOR k IN 1..iLine_t_count LOOP
2747: dbms_sql.column_value ( Line_t_sel_c, k, l_Line_t_tbl(k).value );
2748: END LOOP;
2749:
2750: xProgress := '2710-60';
2751: dbms_sql.column_value ( Line_t_sel_c,
2747: dbms_sql.column_value ( Line_t_sel_c, k, l_Line_t_tbl(k).value );
2748: END LOOP;
2749:
2750: xProgress := '2710-60';
2751: dbms_sql.column_value ( Line_t_sel_c,
2752: iLine_t_count + 1,
2753: rLine_t_rowid );
2754:
2755: xProgress := '2712-60';
2752: iLine_t_count + 1,
2753: rLine_t_rowid );
2754:
2755: xProgress := '2712-60';
2756: dbms_sql.column_value ( Line_t_sel_c,
2757: iLine_t_count + 2,
2758: rLine_t_X_rowid );
2759:
2760: xProgress := '2720-60';
2789:
2790:
2791:
2792: xProgress := '2750-60';
2793: dbms_sql.bind_variable ( Line_t_del_c1,
2794: 'col_rowid',
2795: rLine_t_rowid );
2796:
2797: xProgress := '2752-60';
2794: 'col_rowid',
2795: rLine_t_rowid );
2796:
2797: xProgress := '2752-60';
2798: dbms_sql.bind_variable ( Line_t_del_c2,
2799: 'col_rowid',
2800: rLine_t_X_rowid );
2801:
2802: xProgress := '2760-60';
2799: 'col_rowid',
2800: rLine_t_X_rowid );
2801:
2802: xProgress := '2760-60';
2803: dummy := dbms_sql.execute ( Line_t_del_c1 );
2804:
2805: xProgress := '2762-60';
2806: dummy := dbms_sql.execute ( Line_t_del_c2 );
2807:
2802: xProgress := '2760-60';
2803: dummy := dbms_sql.execute ( Line_t_del_c1 );
2804:
2805: xProgress := '2762-60';
2806: dummy := dbms_sql.execute ( Line_t_del_c2 );
2807:
2808: END LOOP;
2809:
2810: xProgress := '2764-60';
2807:
2808: END LOOP;
2809:
2810: xProgress := '2764-60';
2811: IF ( dbms_sql.last_row_count = 0 )
2812: THEN
2813: v_LevelProcessed := 'LINE TAX';
2814: ec_debug.pl ( 1,
2815: 'EC',
2826: **** -- line tax loop ends here
2827: ****/
2828:
2829: xProgress := '2770-60';
2830: dummy := dbms_sql.execute ( Alw_chg_l_sel_c );
2831:
2832: /*****
2833: ***** -- Allowances and Charges Line loop starts here
2834: *****/
2833: ***** -- Allowances and Charges Line loop starts here
2834: *****/
2835:
2836: xProgress := '2780-60';
2837: WHILE dbms_sql.fetch_rows ( Alw_chg_l_sel_c ) > 0
2838: LOOP --- Allowances and Charges Line
2839:
2840: /*****
2841: ***** store values in pl/sql table
2843:
2844: xProgress := '2790-60';
2845: FOR n IN 1..iAlw_chg_l_count
2846: LOOP
2847: dbms_sql.column_value ( Alw_chg_l_sel_c,
2848: n,
2849: l_alw_chg_l_tbl(n).value );
2850: END LOOP;
2851:
2849: l_alw_chg_l_tbl(n).value );
2850: END LOOP;
2851:
2852: xProgress := '2800-60';
2853: dbms_sql.column_value ( Alw_chg_l_sel_c,
2854: iAlw_chg_l_count + 1,
2855: rAlw_chg_l_rowid );
2856:
2857: xProgress := '2810-60';
2854: iAlw_chg_l_count + 1,
2855: rAlw_chg_l_rowid );
2856:
2857: xProgress := '2810-60';
2858: dbms_sql.column_value ( Alw_chg_l_sel_c,
2859: iAlw_chg_l_count + 2,
2860: rAlw_chg_X_rowid );
2861:
2862: xProgress := '2820-60';
2876: **** -- Use rowid for delete
2877: ****/
2878:
2879: xProgress := '2830-60';
2880: dbms_sql.bind_variable ( Alw_chg_l_del_c1,
2881: 'col_rowid',
2882: rAlw_chg_l_rowid );
2883:
2884: xProgress := '2832-60';
2881: 'col_rowid',
2882: rAlw_chg_l_rowid );
2883:
2884: xProgress := '2832-60';
2885: dbms_sql.bind_variable ( Alw_chg_l_del_c2,
2886: 'col_rowid',
2887: rAlw_chg_X_rowid );
2888:
2889: xProgress := '2834-60';
2886: 'col_rowid',
2887: rAlw_chg_X_rowid );
2888:
2889: xProgress := '2834-60';
2890: dummy := dbms_sql.execute ( Alw_chg_l_del_c1 );
2891:
2892: xProgress := '2836-60';
2893: dummy := dbms_sql.execute ( Alw_chg_l_del_c2 );
2894:
2889: xProgress := '2834-60';
2890: dummy := dbms_sql.execute ( Alw_chg_l_del_c1 );
2891:
2892: xProgress := '2836-60';
2893: dummy := dbms_sql.execute ( Alw_chg_l_del_c2 );
2894:
2895: END LOOP;
2896:
2897: xProgress := '2838-60';
2894:
2895: END LOOP;
2896:
2897: xProgress := '2838-60';
2898: IF ( dbms_sql.last_row_count = 0 ) THEN
2899: v_LevelProcessed := 'ALLOWANCE CHARAGES LINE';
2900: ec_debug.pl ( 1,
2901: 'EC',
2902: 'ECE_NO_DB_ROW_PROCESSED',
2912: **** -- Use rowid for delete
2913: ****/
2914:
2915: xProgress := '2840-60';
2916: dbms_sql.bind_variable ( Line_del_c1,
2917: 'col_rowid',
2918: rLine_rowid );
2919:
2920: xProgress := '2842-60';
2917: 'col_rowid',
2918: rLine_rowid );
2919:
2920: xProgress := '2842-60';
2921: dbms_sql.bind_variable ( Line_del_c2,
2922: 'col_rowid',
2923: rLine_X_rowid );
2924:
2925: xProgress := '2850-60';
2922: 'col_rowid',
2923: rLine_X_rowid );
2924:
2925: xProgress := '2850-60';
2926: dummy := dbms_sql.execute ( Line_del_c1 );
2927:
2928: xProgress := '2852-60';
2929: dummy := dbms_sql.execute ( Line_del_c2 );
2930:
2925: xProgress := '2850-60';
2926: dummy := dbms_sql.execute ( Line_del_c1 );
2927:
2928: xProgress := '2852-60';
2929: dummy := dbms_sql.execute ( Line_del_c2 );
2930:
2931: END LOOP;
2932:
2933: xProgress := '2854-60';
2930:
2931: END LOOP;
2932:
2933: xProgress := '2854-60';
2934: IF ( dbms_sql.last_row_count = 0 )
2935: THEN
2936: v_LevelProcessed := 'LINE';
2937: ec_debug.pl ( 1,
2938: 'EC',
2954: */
2955:
2956: /****
2957: xProgress := '2860-60';
2958: dbms_sql.bind_variable ( Header_1_del_c1,
2959: 'col_rowid',
2960: rHeader_1_rowid );
2961:
2962: xProgress := '2862-60';
2959: 'col_rowid',
2960: rHeader_1_rowid );
2961:
2962: xProgress := '2862-60';
2963: dbms_sql.bind_variable ( Header_1_del_c2,
2964: 'col_rowid',
2965: rHeader_1_X_rowid );
2966:
2967: xProgress := '2870-60';
2964: 'col_rowid',
2965: rHeader_1_X_rowid );
2966:
2967: xProgress := '2870-60';
2968: dummy := dbms_sql.execute ( Header_1_del_c1 );
2969:
2970: xProgress := '2872-60';
2971: dummy := dbms_sql.execute ( Header_1_del_c2 );
2972:
2967: xProgress := '2870-60';
2968: dummy := dbms_sql.execute ( Header_1_del_c1 );
2969:
2970: xProgress := '2872-60';
2971: dummy := dbms_sql.execute ( Header_1_del_c2 );
2972:
2973: END LOOP;
2974:
2975: xProgress := '2874-60';
2972:
2973: END LOOP;
2974:
2975: xProgress := '2874-60';
2976: IF ( dbms_sql.last_row_count = 0 ) THEN
2977: v_LevelProcessed := 'HEADER 1';
2978: ec_debug.pl ( 1,
2979: 'EC',
2980: 'ECE_NO_DB_ROW_PROCESSED',
2991: *** -- header 1 loop ends here
2992: ***/
2993:
2994: xProgress := '2880-60';
2995: dbms_sql.bind_variable ( Header_del_c1,
2996: 'col_rowid',
2997: rHeader_rowid );
2998:
2999: xProgress := '2882-60';
2996: 'col_rowid',
2997: rHeader_rowid );
2998:
2999: xProgress := '2882-60';
3000: dbms_sql.bind_variable ( Header_del_c2,
3001: 'col_rowid',
3002: rHeader_X_rowid );
3003:
3004: xProgress := '2890-60';
3001: 'col_rowid',
3002: rHeader_X_rowid );
3003:
3004: xProgress := '2890-60';
3005: dummy := dbms_sql.execute ( Header_del_c1 );
3006:
3007: xProgress := '2892-60';
3008: dummy := dbms_sql.execute ( Header_del_c2 );
3009:
3004: xProgress := '2890-60';
3005: dummy := dbms_sql.execute ( Header_del_c1 );
3006:
3007: xProgress := '2892-60';
3008: dummy := dbms_sql.execute ( Header_del_c2 );
3009:
3010: END LOOP;
3011:
3012: xProgress := '2894-60';
3009:
3010: END LOOP;
3011:
3012: xProgress := '2894-60';
3013: IF ( dbms_sql.last_row_count = 0 ) THEN
3014: v_LevelProcessed := 'HEADER';
3015: ec_debug.pl ( 1,
3016: 'EC',
3017: 'ECE_NO_DB_ROW_PROCESSED',
3036: /***
3037: *** -- close all open cursors here
3038: ***/
3039: xProgress := '2910-60';
3040: dbms_sql.close_cursor ( Header_sel_c );
3041:
3042: xProgress := '2911-60';
3043: dbms_sql.close_cursor ( Header_1_sel_c );
3044:
3039: xProgress := '2910-60';
3040: dbms_sql.close_cursor ( Header_sel_c );
3041:
3042: xProgress := '2911-60';
3043: dbms_sql.close_cursor ( Header_1_sel_c );
3044:
3045: xProgress := '2912-60';
3046: dbms_sql.close_cursor ( Alw_chg_h_sel_c );
3047:
3042: xProgress := '2911-60';
3043: dbms_sql.close_cursor ( Header_1_sel_c );
3044:
3045: xProgress := '2912-60';
3046: dbms_sql.close_cursor ( Alw_chg_h_sel_c );
3047:
3048: xProgress := '2913-60';
3049: dbms_sql.close_cursor ( Line_sel_c );
3050:
3045: xProgress := '2912-60';
3046: dbms_sql.close_cursor ( Alw_chg_h_sel_c );
3047:
3048: xProgress := '2913-60';
3049: dbms_sql.close_cursor ( Line_sel_c );
3050:
3051: xProgress := '2914-60';
3052: dbms_sql.close_cursor ( Line_t_sel_c );
3053:
3048: xProgress := '2913-60';
3049: dbms_sql.close_cursor ( Line_sel_c );
3050:
3051: xProgress := '2914-60';
3052: dbms_sql.close_cursor ( Line_t_sel_c );
3053:
3054: xProgress := '2915-60';
3055: dbms_sql.close_cursor ( Alw_chg_l_sel_c );
3056:
3051: xProgress := '2914-60';
3052: dbms_sql.close_cursor ( Line_t_sel_c );
3053:
3054: xProgress := '2915-60';
3055: dbms_sql.close_cursor ( Alw_chg_l_sel_c );
3056:
3057: xProgress := '2916-60';
3058: dbms_sql.close_cursor ( Header_del_c1 );
3059:
3054: xProgress := '2915-60';
3055: dbms_sql.close_cursor ( Alw_chg_l_sel_c );
3056:
3057: xProgress := '2916-60';
3058: dbms_sql.close_cursor ( Header_del_c1 );
3059:
3060: xProgress := '2917-60';
3061: dbms_sql.close_cursor ( Header_1_del_c1 );
3062:
3057: xProgress := '2916-60';
3058: dbms_sql.close_cursor ( Header_del_c1 );
3059:
3060: xProgress := '2917-60';
3061: dbms_sql.close_cursor ( Header_1_del_c1 );
3062:
3063: xProgress := '2918-60';
3064: dbms_sql.close_cursor ( Alw_chg_h_del_c1 );
3065:
3060: xProgress := '2917-60';
3061: dbms_sql.close_cursor ( Header_1_del_c1 );
3062:
3063: xProgress := '2918-60';
3064: dbms_sql.close_cursor ( Alw_chg_h_del_c1 );
3065:
3066: xProgress := '2919-60';
3067: dbms_sql.close_cursor ( Line_del_c1 );
3068:
3063: xProgress := '2918-60';
3064: dbms_sql.close_cursor ( Alw_chg_h_del_c1 );
3065:
3066: xProgress := '2919-60';
3067: dbms_sql.close_cursor ( Line_del_c1 );
3068:
3069: xProgress := '2920-60';
3070: dbms_sql.close_cursor ( Line_t_del_c1 );
3071:
3066: xProgress := '2919-60';
3067: dbms_sql.close_cursor ( Line_del_c1 );
3068:
3069: xProgress := '2920-60';
3070: dbms_sql.close_cursor ( Line_t_del_c1 );
3071:
3072: xProgress := '2921-60';
3073: dbms_sql.close_cursor ( Alw_chg_l_del_c1 );
3074:
3069: xProgress := '2920-60';
3070: dbms_sql.close_cursor ( Line_t_del_c1 );
3071:
3072: xProgress := '2921-60';
3073: dbms_sql.close_cursor ( Alw_chg_l_del_c1 );
3074:
3075: ec_debug.pop('ece_ar_transaction.Put_Data_To_Output_Table');
3076:
3077: EXCEPTION
3515: -- :transaction_id is a place holder for foreign key value.
3516: -- A PL/SQL table (list of values) will be used to store data.
3517: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
3518: -- data value in the PL/SQL table.
3519: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
3520: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
3521: --
3522: -- Let's use the above example:
3523: --
3516: -- A PL/SQL table (list of values) will be used to store data.
3517: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
3518: -- data value in the PL/SQL table.
3519: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
3520: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
3521: --
3522: -- Let's use the above example:
3523: --
3524: -- 1. Execute dynamic SQL 1 for headers (A) data
3683: -- ***
3684: -- ***************************************************
3685:
3686: xProgress := '2350-70';
3687: Header_sel_c := dbms_sql.open_cursor;
3688:
3689: xProgress := '2352-70';
3690: Header_1_sel_c := dbms_sql.open_cursor;
3691:
3686: xProgress := '2350-70';
3687: Header_sel_c := dbms_sql.open_cursor;
3688:
3689: xProgress := '2352-70';
3690: Header_1_sel_c := dbms_sql.open_cursor;
3691:
3692: xProgress := '2354-70';
3693: Alw_chg_sel_c := dbms_sql.open_cursor;
3694:
3689: xProgress := '2352-70';
3690: Header_1_sel_c := dbms_sql.open_cursor;
3691:
3692: xProgress := '2354-70';
3693: Alw_chg_sel_c := dbms_sql.open_cursor;
3694:
3695: xProgress := '2356-70';
3696: Line_sel_c := dbms_sql.open_cursor;
3697:
3692: xProgress := '2354-70';
3693: Alw_chg_sel_c := dbms_sql.open_cursor;
3694:
3695: xProgress := '2356-70';
3696: Line_sel_c := dbms_sql.open_cursor;
3697:
3698: xProgress := '2358-70';
3699: Line_t_sel_c := dbms_sql.open_cursor;
3700:
3695: xProgress := '2356-70';
3696: Line_sel_c := dbms_sql.open_cursor;
3697:
3698: xProgress := '2358-70';
3699: Line_t_sel_c := dbms_sql.open_cursor;
3700:
3701: -- ***************************************************
3702: --
3703: -- Parse each of the SELECT statement
3706: -- ***************************************************
3707:
3708: xProgress := '2360-70';
3709: BEGIN
3710: dbms_sql.parse ( Header_sel_c,
3711: cHeader_select,
3712: dbms_sql.native );
3713: EXCEPTION
3714: WHEN OTHERS THEN
3708: xProgress := '2360-70';
3709: BEGIN
3710: dbms_sql.parse ( Header_sel_c,
3711: cHeader_select,
3712: dbms_sql.native );
3713: EXCEPTION
3714: WHEN OTHERS THEN
3715: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3716: cHeader_select );
3711: cHeader_select,
3712: dbms_sql.native );
3713: EXCEPTION
3714: WHEN OTHERS THEN
3715: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3716: cHeader_select );
3717: app_exception.raise_exception;
3718: END;
3719:
3718: END;
3719:
3720: xProgress := '2362-70';
3721: BEGIN
3722: dbms_sql.parse ( Header_1_sel_c,
3723: cHeader_1_select,
3724: dbms_sql.native );
3725: EXCEPTION
3726: WHEN OTHERS THEN
3720: xProgress := '2362-70';
3721: BEGIN
3722: dbms_sql.parse ( Header_1_sel_c,
3723: cHeader_1_select,
3724: dbms_sql.native );
3725: EXCEPTION
3726: WHEN OTHERS THEN
3727: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3728: cHeader_1_select );
3723: cHeader_1_select,
3724: dbms_sql.native );
3725: EXCEPTION
3726: WHEN OTHERS THEN
3727: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3728: cHeader_1_select );
3729: app_exception.raise_exception;
3730: END;
3731:
3730: END;
3731:
3732: xProgress := '2364-70';
3733: BEGIN
3734: dbms_sql.parse ( Alw_chg_sel_c,
3735: cAlw_chg_select,
3736: dbms_sql.native );
3737: EXCEPTION
3738: WHEN OTHERS THEN
3732: xProgress := '2364-70';
3733: BEGIN
3734: dbms_sql.parse ( Alw_chg_sel_c,
3735: cAlw_chg_select,
3736: dbms_sql.native );
3737: EXCEPTION
3738: WHEN OTHERS THEN
3739: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3740: cAlw_chg_select );
3735: cAlw_chg_select,
3736: dbms_sql.native );
3737: EXCEPTION
3738: WHEN OTHERS THEN
3739: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3740: cAlw_chg_select );
3741: app_exception.raise_exception;
3742: END;
3743:
3742: END;
3743:
3744: xProgress := '2366-70';
3745: BEGIN
3746: dbms_sql.parse ( Line_sel_c,
3747: cLine_select,
3748: dbms_sql.native );
3749: EXCEPTION
3750: WHEN OTHERS THEN
3744: xProgress := '2366-70';
3745: BEGIN
3746: dbms_sql.parse ( Line_sel_c,
3747: cLine_select,
3748: dbms_sql.native );
3749: EXCEPTION
3750: WHEN OTHERS THEN
3751: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3752: cLine_select );
3747: cLine_select,
3748: dbms_sql.native );
3749: EXCEPTION
3750: WHEN OTHERS THEN
3751: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3752: cLine_select );
3753: app_exception.raise_exception;
3754: END;
3755:
3754: END;
3755:
3756: xProgress := '2368-70';
3757: BEGIN
3758: dbms_sql.parse ( Line_t_sel_c,
3759: cLine_t_select,
3760: dbms_sql.native );
3761: EXCEPTION
3762: WHEN OTHERS THEN
3756: xProgress := '2368-70';
3757: BEGIN
3758: dbms_sql.parse ( Line_t_sel_c,
3759: cLine_t_select,
3760: dbms_sql.native );
3761: EXCEPTION
3762: WHEN OTHERS THEN
3763: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3764: cLine_t_select );
3759: cLine_t_select,
3760: dbms_sql.native );
3761: EXCEPTION
3762: WHEN OTHERS THEN
3763: ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3764: cLine_t_select );
3765: app_exception.raise_exception;
3766: END;
3767:
3833: -- Bind Variables
3834: xProgress := '2388-70';
3835: IF cCreate_Date_From IS NOT NULL
3836: THEN
3837: dbms_sql.bind_variable ( Header_sel_c,
3838: 'l_cCreate_Date_From',
3839: cCreate_Date_From);
3840: END IF;
3841:
3841:
3842: xProgress := '2388-71';
3843: IF cCreate_Date_To IS NOT NULL
3844: THEN
3845: dbms_sql.bind_variable ( Header_sel_c,
3846: 'l_cCreate_Date_To',
3847: cCreate_Date_To);
3848: END IF;
3849:
3849:
3850: xProgress := '2388-72';
3851: IF cCustomer_Name IS NOT NULL
3852: THEN
3853: dbms_sql.bind_variable ( Header_sel_c,
3854: 'l_cCustomer_Name',
3855: cCustomer_Name );
3856: END IF;
3857:
3857:
3858: xProgress := '2388-73';
3859: IF cSite_Use_Code IS NOT NULL
3860: THEN
3861: dbms_sql.bind_variable ( Header_sel_c,
3862: 'l_cSite_Use_Code',
3863: cSite_Use_Code );
3864: END IF;
3865:
3865:
3866: xProgress := '2388-74';
3867: IF cDocument_Type IS NOT NULL
3868: THEN
3869: dbms_sql.bind_variable ( Header_sel_c,
3870: 'l_cDocument_Type',
3871: cDocument_Type );
3872: END IF;
3873:
3873:
3874: xProgress := '2388-75';
3875: IF cTransaction_Number IS NOT NULL
3876: THEN
3877: dbms_sql.bind_variable ( Header_sel_c,
3878: 'l_cTransaction_Number',
3879: cTransaction_Number );
3880: END IF;
3881:
3892: -- ***************************************************
3893: -- EXECUTE the SELECT statement
3894:
3895: xProgress := '2390-70';
3896: dummy := dbms_sql.execute(Header_sel_c);
3897:
3898: -- ***************************************************
3899: --
3900: -- The model is:
3907: --
3908: -- ***************************************************
3909:
3910: xProgress := '2400-70';
3911: WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
3912: LOOP -- Header
3913:
3914: -- ***************************************************
3915: --
3919:
3920: xProgress := '2410-70';
3921: FOR i IN 1..iHeader_count
3922: LOOP
3923: dbms_sql.column_value ( Header_sel_c,
3924: i,
3925: l_header_tbl(i).value );
3926:
3927: dbms_sql.column_value ( Header_sel_c,
3923: dbms_sql.column_value ( Header_sel_c,
3924: i,
3925: l_header_tbl(i).value );
3926:
3927: dbms_sql.column_value ( Header_sel_c,
3928: i,
3929: l_key_tbl(i).value );
3930: END LOOP;
3931:
4477:
4478: -- use the following bind_variable feature as you see fit.
4479:
4480: xProgress := '2610-70';
4481: dbms_sql.bind_variable ( Header_1_sel_c,
4482: 'transaction_id',
4483: l_header_tbl(nPos1).value );
4484:
4485: xProgress := '2612-70';
4482: 'transaction_id',
4483: l_header_tbl(nPos1).value );
4484:
4485: xProgress := '2612-70';
4486: dbms_sql.bind_variable ( Alw_chg_sel_c,
4487: 'transaction_id',
4488: l_header_tbl(nPos1).value );
4489:
4490: xProgress := '2614-70';
4487: 'transaction_id',
4488: l_header_tbl(nPos1).value );
4489:
4490: xProgress := '2614-70';
4491: dbms_sql.bind_variable ( Line_sel_c,
4492: 'transaction_id',
4493: l_header_tbl(nPos1).value );
4494:
4495: xProgress := '2616-70';
4492: 'transaction_id',
4493: l_header_tbl(nPos1).value );
4494:
4495: xProgress := '2616-70';
4496: dbms_sql.bind_variable ( Line_t_sel_c,
4497: 'transaction_id',
4498: l_header_tbl(nPos1).value );
4499:
4500: xProgress := '2620-70';
4497: 'transaction_id',
4498: l_header_tbl(nPos1).value );
4499:
4500: xProgress := '2620-70';
4501: dummy := dbms_sql.execute( Header_1_sel_c );
4502:
4503: -- ***************************************************
4504: --
4505: -- header detail loop starts here
4508:
4509: /* Header 1 loop begins here */
4510:
4511: xProgress := '2630-70';
4512: WHILE dbms_sql.fetch_rows ( Header_1_sel_c ) > 0
4513: LOOP --- Header Detail
4514:
4515: -- ***************************************************
4516: --
4519: -- ***************************************************
4520:
4521: xProgress := '2640-70';
4522: FOR l IN 1..iHeader_1_count LOOP
4523: dbms_sql.column_value ( Header_1_sel_c,
4524: l,
4525: l_header_1_tbl(l).value );
4526:
4527: dbms_sql.column_value ( Header_1_sel_c,
4523: dbms_sql.column_value ( Header_1_sel_c,
4524: l,
4525: l_header_1_tbl(l).value );
4526:
4527: dbms_sql.column_value ( Header_1_sel_c,
4528: l,
4529: l_key_tbl(l+iHeader_count).value );
4530: END LOOP;
4531:
4693: END LOOP;
4694: /* header 1 loop ends here */
4695:
4696: xProgress := '2722-70';
4697: IF ( dbms_sql.last_row_count = 0 ) THEN
4698: v_LevelProcessed := 'HEADER 1';
4699: ec_debug.pl ( 1,
4700: 'EC',
4701: 'ECE_NO_DB_ROW_PROCESSED',
4707: cTransaction_Type );
4708: END IF;
4709:
4710: xProgress := '2730-70';
4711: dummy := dbms_sql.execute (Alw_chg_sel_c );
4712:
4713: -- ***************************************************
4714: --
4715: -- allowance and charges loop starts here
4716: --
4717: -- ***************************************************
4718:
4719: xProgress := '2740-70';
4720: WHILE dbms_sql.fetch_rows (Alw_chg_sel_c ) > 0
4721: LOOP --- Allowance and Charges
4722:
4723: -- ***************************************************
4724: --
4728:
4729: xProgress := '2750-70';
4730: FOR m IN 1..iAlw_chg_count
4731: LOOP
4732: dbms_sql.column_value ( Alw_chg_sel_c,
4733: m,
4734: l_alw_chg_tbl(m).value );
4735:
4736: dbms_sql.column_value ( Alw_chg_sel_c,
4732: dbms_sql.column_value ( Alw_chg_sel_c,
4733: m,
4734: l_alw_chg_tbl(m).value );
4735:
4736: dbms_sql.column_value ( Alw_chg_sel_c,
4737: m,
4738: l_key_tbl(m+iHeader_count).value );
4739: END LOOP;
4740:
4979:
4980: END LOOP; -- allowence and charges
4981:
4982: xProgress := '2854-70';
4983: IF ( dbms_sql.last_row_count = 0 ) THEN
4984: v_LevelProcessed := 'ALLOWANCE CHARGES';
4985: ec_debug.pl ( 1,
4986: 'EC',
4987: 'ECE_NO_DB_ROW_PROCESSED',
4997: -- ALL of you has to create this package in a seperate file
4998: -- even if it is empty.
4999:
5000: xProgress := '2860-70';
5001: dummy := dbms_sql.execute ( Line_sel_c );
5002:
5003: -- ***************************************************
5004: --
5005: -- line loop starts here
5006: --
5007: -- ***************************************************
5008:
5009: xProgress := '2870-70';
5010: WHILE dbms_sql.fetch_rows ( Line_sel_c ) > 0
5011: LOOP --- Line
5012:
5013: -- ***************************************************
5014: --
5018:
5019: xProgress := '2880-70';
5020: FOR j IN 1..iLine_count
5021: LOOP
5022: dbms_sql.column_value ( Line_sel_c,
5023: j,
5024: l_line_tbl(j).value );
5025:
5026: dbms_sql.column_value ( Line_sel_c,
5022: dbms_sql.column_value ( Line_sel_c,
5023: j,
5024: l_line_tbl(j).value );
5025:
5026: dbms_sql.column_value ( Line_sel_c,
5027: j,
5028: l_key_tbl(j+iHeader_count).value );
5029: END LOOP;
5030:
5222: --
5223: -- ***************************************************
5224:
5225: xProgress := '2960-70';
5226: dbms_sql.bind_variable (Line_t_sel_c,
5227: 'LINE_NUMBER',
5228: l_line_tbl(nPos9).value );
5229:
5230: xProgress := '2970-70';
5227: 'LINE_NUMBER',
5228: l_line_tbl(nPos9).value );
5229:
5230: xProgress := '2970-70';
5231: dummy := dbms_sql.execute ( Line_t_sel_c );
5232:
5233: -- ***************************************************
5234: --
5235: -- line tax loop starts here
5236: --
5237: -- ***************************************************
5238:
5239: xProgress := '2980-70';
5240: WHILE dbms_sql.fetch_rows ( Line_t_sel_c ) > 0
5241: LOOP --- Line Tax
5242:
5243: -- ***************************************************
5244: --
5248:
5249: xProgress := '2990-70';
5250: FOR k IN 1..iLine_t_count
5251: LOOP
5252: dbms_sql.column_value ( Line_t_sel_c,
5253: k,
5254: l_line_t_tbl(k).value );
5255:
5256: dbms_sql.column_value ( Line_t_sel_c,
5252: dbms_sql.column_value ( Line_t_sel_c,
5253: k,
5254: l_line_t_tbl(k).value );
5255:
5256: dbms_sql.column_value ( Line_t_sel_c,
5257: k,
5258: l_key_tbl(k+iHeader_count+iLine_count).value );
5259: END LOOP;
5260:
5339:
5340: END LOOP;
5341:
5342: xProgress := '3042-70';
5343: IF ( dbms_sql.last_row_count = 0 )
5344: THEN
5345: v_LevelProcessed := 'LINE TAX';
5346: ec_debug.pl ( 1,
5347: 'EC',
5356:
5357: END LOOP;
5358:
5359: xProgress := '3044-70';
5360: IF ( dbms_sql.last_row_count = 0 )
5361: THEN
5362: v_LevelProcessed := 'LINE';
5363: ec_debug.pl ( 1,
5364: 'EC',
5474: /*****
5475: END LOOP;
5476:
5477: xProgress := '3046-70';
5478: IF ( dbms_sql.last_row_count = 0 ) THEN
5479: v_LevelProcessed := 'HEADER 1';
5480: ec_debug.pl ( 1,
5481: 'EC',
5482: 'ECE_NO_DB_ROW_PROCESSED',
5490: *****/
5491: END LOOP;
5492:
5493: xProgress := '3048-70';
5494: IF ( dbms_sql.last_row_count = 0 ) THEN
5495: v_LevelProcessed := 'HEADER';
5496: ec_debug.pl ( 1,
5497: 'EC',
5498: 'ECE_NO_DB_ROW_PROCESSED',
5507: xProgress := '3050-70';
5508: -- COMMIT;
5509:
5510: xProgress := '3060-70';
5511: dbms_sql.close_cursor ( Header_sel_c );
5512:
5513: xProgress := '3062-70';
5514: dbms_sql.close_cursor ( Header_1_sel_c );
5515:
5510: xProgress := '3060-70';
5511: dbms_sql.close_cursor ( Header_sel_c );
5512:
5513: xProgress := '3062-70';
5514: dbms_sql.close_cursor ( Header_1_sel_c );
5515:
5516: xProgress := '3064-70';
5517: dbms_sql.close_cursor ( Alw_chg_sel_c );
5518:
5513: xProgress := '3062-70';
5514: dbms_sql.close_cursor ( Header_1_sel_c );
5515:
5516: xProgress := '3064-70';
5517: dbms_sql.close_cursor ( Alw_chg_sel_c );
5518:
5519: xProgress := '3066-70';
5520: dbms_sql.close_cursor ( Line_sel_c );
5521:
5516: xProgress := '3064-70';
5517: dbms_sql.close_cursor ( Alw_chg_sel_c );
5518:
5519: xProgress := '3066-70';
5520: dbms_sql.close_cursor ( Line_sel_c );
5521:
5522: xProgress := '3068-70';
5523: dbms_sql.close_cursor ( Line_t_sel_c );
5524:
5519: xProgress := '3066-70';
5520: dbms_sql.close_cursor ( Line_sel_c );
5521:
5522: xProgress := '3068-70';
5523: dbms_sql.close_cursor ( Line_t_sel_c );
5524:
5525: ec_debug.pop ( 'ece_ar_transaction.Populate_AR_Trx' );
5526:
5527: EXCEPTION