226: IS
227:
228: cursor cur_limits_breached(p_log_id IN NUMBER) is
229: select xle.deal_number, xle.transaction_number, decode(xle.exception_type,
230: 'EXCEEDED', FND_MESSAGE.GET_STRING('XTR','XTR_2242'), 'WARNING', FND_MESSAGE.GET_STRING('XTR','XTR_2241'), 'NO_AUTHO',
231: FND_MESSAGE.GET_STRING('XTR','XTR_2243'), 'NO_LIMIT', FND_MESSAGE.GET_STRING('XTR','XTR_2244')) as exception_type,
232: decode(xle.exception_type, 'EXCEEDED', 'XTR_2245',
233: 'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
234: 'NO_LIMIT','XTR_2248') as exception_desc,
227:
228: cursor cur_limits_breached(p_log_id IN NUMBER) is
229: select xle.deal_number, xle.transaction_number, decode(xle.exception_type,
230: 'EXCEEDED', FND_MESSAGE.GET_STRING('XTR','XTR_2242'), 'WARNING', FND_MESSAGE.GET_STRING('XTR','XTR_2241'), 'NO_AUTHO',
231: FND_MESSAGE.GET_STRING('XTR','XTR_2243'), 'NO_LIMIT', FND_MESSAGE.GET_STRING('XTR','XTR_2244')) as exception_type,
232: decode(xle.exception_type, 'EXCEEDED', 'XTR_2245',
233: 'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
234: 'NO_LIMIT','XTR_2248') as exception_desc,
235: decode(xle.LIMIT_CHECK_TYPE,
232: decode(xle.exception_type, 'EXCEEDED', 'XTR_2245',
233: 'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
234: 'NO_LIMIT','XTR_2248') as exception_desc,
235: decode(xle.LIMIT_CHECK_TYPE,
236: 'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
233: 'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
234: 'NO_LIMIT','XTR_2248') as exception_desc,
235: decode(xle.LIMIT_CHECK_TYPE,
236: 'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
234: 'NO_LIMIT','XTR_2248') as exception_desc,
235: decode(xle.LIMIT_CHECK_TYPE,
236: 'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
235: decode(xle.LIMIT_CHECK_TYPE,
236: 'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243: 'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
236: 'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243: 'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
244: xle.limit_code,
237: 'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243: 'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
244: xle.limit_code,
245: xle.exceeded_by_amount exceeded_by_amount_dsp,
238: 'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243: 'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
244: xle.limit_code,
245: xle.exceeded_by_amount exceeded_by_amount_dsp,
246: xle.exceeded_on_date exceeded_on_date_dsp,
239: 'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240: 'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241: 'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242: 'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243: 'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
244: xle.limit_code,
245: xle.exceeded_by_amount exceeded_by_amount_dsp,
246: xle.exceeded_on_date exceeded_on_date_dsp,
247: xle.currency, xle.company_code, xle.limit_party,
269: l_ccy_code VARCHAR2(15);
270: l_exc_desc VARCHAR2(100);
271:
272: BEGIN
273: l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
270: l_exc_desc VARCHAR2(100);
271:
272: BEGIN
273: l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
271:
272: BEGIN
273: l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
272: BEGIN
273: l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
273: l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
274: l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
275: l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
276: l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
277: l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
278: l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286: l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
279: l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286: l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
287:
280: l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286: l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
287:
288:
281: l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286: l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
287:
288:
289: SELECT nvl(param_value,'USD')
282: l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283: l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284: l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285: l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286: l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
287:
288:
289: SELECT nvl(param_value,'USD')
290: INTO l_ccy_code
329: rpad(l_auth_by,28)||
330: rpad(l_dealer_code,28)||FND_GLOBAL.NEWLINE;
331: end if;
332: for limit_rec in cur_limits_breached(to_number(document_id)) loop
333: FND_MESSAGE.set_name('XTR', limit_rec.exception_desc);
334: FND_MESSAGE.set_token('LIMIT',limit_rec.exception_token );
335: l_exc_desc := FND_MESSAGE.get;
336: if display_type = 'text/html' then
337: document := document||
330: rpad(l_dealer_code,28)||FND_GLOBAL.NEWLINE;
331: end if;
332: for limit_rec in cur_limits_breached(to_number(document_id)) loop
333: FND_MESSAGE.set_name('XTR', limit_rec.exception_desc);
334: FND_MESSAGE.set_token('LIMIT',limit_rec.exception_token );
335: l_exc_desc := FND_MESSAGE.get;
336: if display_type = 'text/html' then
337: document := document||
338: '
331: end if;
332: for limit_rec in cur_limits_breached(to_number(document_id)) loop
333: FND_MESSAGE.set_name('XTR', limit_rec.exception_desc);
334: FND_MESSAGE.set_token('LIMIT',limit_rec.exception_token );
335: l_exc_desc := FND_MESSAGE.get;
336: if display_type = 'text/html' then
337: document := document||
338: '