[Home] [Help]
2004: -- 29-JUN-2006 Added a parameter to store the unit price of tax currency
2005: -- by Shujuan for bug 5168900
2006: p_tax_curr_unit_price NUMBER;
2007:
2008: -- a prefix of parameter in bind_variable command of dbms sql.
2009: l_arg VARCHAR2(10);
2010:
2011: -- a flag which adjust the trx record is new,
2012: -- if dul_flag>1, the trx record is old ,else is new
2227: END;
2228: --end select parameters
2229:
2230: --open cursor
2231: l_cursor := dbms_sql.open_cursor;
2232:
2233: --parse the sql string;
2234: dbms_sql.parse(l_cursor, p_query_sql, dbms_sql.v7);
2235:
2230: --open cursor
2231: l_cursor := dbms_sql.open_cursor;
2232:
2233: --parse the sql string;
2234: dbms_sql.parse(l_cursor, p_query_sql, dbms_sql.v7);
2235:
2236: -- bind variable;
2237: dbms_sql.bind_variable(l_cursor, 'p_org_id', p_org_id);
2238:
2233: --parse the sql string;
2234: dbms_sql.parse(l_cursor, p_query_sql, dbms_sql.v7);
2235:
2236: -- bind variable;
2237: dbms_sql.bind_variable(l_cursor, 'p_org_id', p_org_id);
2238:
2239: -- bind trxtype_query_para
2240: l_index := p_trxtype_query_para.FIRST;
2241:
2240: l_index := p_trxtype_query_para.FIRST;
2241:
2242: WHILE l_index IS NOT NULL LOOP
2243: l_arg := 'trxtype' || l_index;
2244: dbms_sql.bind_variable(l_cursor,
2245: l_arg,
2246: p_trxtype_query_para(l_index));
2247: l_index := P_trxtype_query_para.NEXT(l_index);
2248: END LOOP;
2250: -- bind flex_query_para
2251: l_index := p_flex_query_para.FIRST;
2252: WHILE l_index IS NOT NULL LOOP
2253: l_arg := 'flex' || l_index;
2254: dbms_sql.bind_variable(l_cursor, l_arg, p_flex_query_para(l_index));
2255: l_index := p_flex_query_para.NEXT(l_index);
2256: END LOOP;
2257:
2258: -- bind other_query_para
2258: -- bind other_query_para
2259: l_index := p_other_query_para.FIRST;
2260: WHILE l_index IS NOT NULL LOOP
2261: l_arg := 'para' || l_index;
2262: dbms_sql.bind_variable(l_cursor, l_arg, p_other_query_para(l_index));
2263: l_index := p_other_query_para.NEXT(l_index);
2264: END LOOP;
2265:
2266: -- bind the condition of currency code.
2264: END LOOP;
2265:
2266: -- bind the condition of currency code.
2267: IF p_currency_code IS NOT NULL THEN
2268: dbms_sql.bind_variable(l_cursor, 'currency', p_currency_code);
2269: END IF;
2270:
2271: --define column
2272: dbms_sql.define_column(l_cursor, 1, l_customer_trx_id);
2268: dbms_sql.bind_variable(l_cursor, 'currency', p_currency_code);
2269: END IF;
2270:
2271: --define column
2272: dbms_sql.define_column(l_cursor, 1, l_customer_trx_id);
2273:
2274: --EXECUTE!
2275: l_sql_exec_ret := dbms_sql.EXECUTE(l_cursor);
2276:
2271: --define column
2272: dbms_sql.define_column(l_cursor, 1, l_customer_trx_id);
2273:
2274: --EXECUTE!
2275: l_sql_exec_ret := dbms_sql.EXECUTE(l_cursor);
2276:
2277: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2278: log(FND_LOG.LEVEL_PROCEDURE,
2279: G_MODULE_PREFIX || l_procedure_name,
2283: LOOP
2284: BEGIN
2285: -- for l_normal_exception
2286:
2287: l_sql_exec_ret := dbms_sql.fetch_rows(l_cursor);
2288:
2289: --initializing all the varibales required for exception
2290: l_ar_inv_excep := null;
2291: l_cm_excep := null;
2304: l_trx_lines := JMF_GTA_TRX_UTIL.TRX_line_tbl_TYPE();
2305: l_error_flag := 0;
2306:
2307: -- get customer_trx_id of the AR_trx_header
2308: dbms_sql.column_value(l_cursor, 1, l_customer_trx_id);
2309:
2310: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2311: log(FND_LOG.LEVEL_PROCEDURE,
2312: G_MODULE_PREFIX || l_procedure_name,
3857:
3858: END LOOP;
3859:
3860: -- close dynamic sql cursor
3861: dbms_sql.close_cursor(l_cursor);
3862:
3863: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3864: fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
3865: G_MODULE_PREFIX || l_procedure_name,