eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.XTR_AUTO_RECONCILIATION dependencies on XTR_DEAL_DATE_AMOUNTS

Line 311: from XTR_DEAL_DATE_AMOUNTS_V

307: --
308: cursor DDA_REC_ROW is
309: select DEAL_NUMBER,TRANSACTION_NUMBER,AMOUNT,AMOUNT_DATE,
310: RECONCILED_REFERENCE,RECONCILED_PASS_CODE --reset -- AW 1/6/2000 Bug 1139396
311: from XTR_DEAL_DATE_AMOUNTS_V
312: where RECONCILED_REFERENCE between
313: to_number(nvl(p_min_rec_nos,9999999)) and
314: to_number(nvl(p_max_rec_nos,0))
315: --- and AMOUNT_TYPE = 'PRINFLW'

Line 351: update XTR_DEAL_DATE_AMOUNTS_V

347: old_rec_pass);
348: l_lowest_start := NULL;
349: end if;
350: end if;
351: update XTR_DEAL_DATE_AMOUNTS_V
352: set AMOUNT = 0,
353: CASHFLOW_AMOUNT = 0,
354: DATE_TYPE = 'FORCAST' -- AW 1/6/2000 Bug 1139396
355: where DEAL_NUMBER = l_deal_no

Line 435: from XTR_DEAL_DATE_AMOUNTS_V dda

431: ---------
432: CURSOR DDA_REC_ROW is
433: select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
434: dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
435: from XTR_DEAL_DATE_AMOUNTS_V dda
436: where dda.RECONCILED_REFERENCE between
437: nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
438: and dda.DEAL_TYPE = 'RTMM'
439: and nvl(dda.amount,0) <>0

Line 460: IN (SELECT settlement_number FROM xtr_deal_date_amounts WHERE reconciled_reference = p_rec_ref )));

456: CURSOR SETTLE_DATE(p_rec_ref NUMBER) is
457: SELECT effective_date FROM ce_statement_lines WHERE statement_line_id IN (
458: SELECT statement_line_id FROM ce_statement_reconcils_all WHERE reference_id in
459: (SELECT settlement_summary_id FROM xtr_settlement_summary WHERE settlement_number
460: IN (SELECT settlement_number FROM xtr_deal_date_amounts WHERE reconciled_reference = p_rec_ref )));
461: /* select VALUE_DATE
462: from XTR_PAY_REC_RECONCILIATION_V
463: where RECONCILED_REFERENCE = p_rec_ref ; */
464: --

Line 604: from XTR_DEAL_DATE_AMOUNTS_V dda

600: ---------
601: CURSOR DDA_REC_ROW is
602: select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
603: dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
604: from XTR_DEAL_DATE_AMOUNTS_V dda
605: where dda.RECONCILED_REFERENCE between
606: nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
607: and dda.DEAL_TYPE = 'RTMM'
608: and nvl(dda.amount,0) <>0

Line 987: from xtr_deal_date_amounts_v

983: -- Bug 4226409
984: cursor cur_settle_detail is
985: select actual_settlement_date,settlement_number,deal_type,trans_mts
986: ,settlement_authorised_by,audit_indicator
987: from xtr_deal_date_amounts_v
988: where deal_number = l_deal_no
989: and transaction_number = pmt.transaction_number
990: and amount_type = 'INTSET';
991:

Line 994: l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.

990: and amount_type = 'INTSET';
991:
992: l_settle_number number; -- added for bug 4226409.
993: l_settle_date date; -- added for bug 4226409.
994: l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.
995: l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.
996: l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.
997: l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.
998:

Line 995: l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.

991:
992: l_settle_number number; -- added for bug 4226409.
993: l_settle_date date; -- added for bug 4226409.
994: l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.
995: l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.
996: l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.
997: l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.
998:
999:

Line 996: l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.

992: l_settle_number number; -- added for bug 4226409.
993: l_settle_date date; -- added for bug 4226409.
994: l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.
995: l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.
996: l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.
997: l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.
998:
999:
1000: begin

Line 997: l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.

993: l_settle_date date; -- added for bug 4226409.
994: l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.
995: l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.
996: l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.
997: l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.
998:
999:
1000: begin
1001:

Line 1299: update XTR_DEAL_DATE_AMOUNTS_V

1295: null;
1296: End if;
1297: --
1298: -- Update Interest Amounts
1299: update XTR_DEAL_DATE_AMOUNTS_V
1300: set AMOUNT = round(decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1301: ,0,nvl(pmt.PI_AMOUNT_DUE,0)
1302: ,nvl(pmt.INTEREST_SETTLED,0)),
1303: rounding_fac),

Line 1330: update XTR_DEAL_DATE_AMOUNTS_V

1326: End if;
1327: --
1328: -- Principal Repayment has/will ocurr ???
1329: if nvl(l_prin_decr,0)<>0 then
1330: update XTR_DEAL_DATE_AMOUNTS_V
1331: set AMOUNT = decode(nvl(AMOUNT,0),0,nvl(l_prin_decr,0),AMOUNT),
1332: HCE_AMOUNT = decode(nvl(HCE_AMOUNT,0),0,hce_decr,HCE_AMOUNT),
1333: AMOUNT_DATE = nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
1334: DATE_TYPE = decode(nvl(pmt.PI_AMOUNT_RECEIVED,0),0,'FORCAST','SETTLE'),

Line 1353: update XTR_DEAL_DATE_AMOUNTS_V

1349: fetch cur_settle_detail into l_settle_date,l_settle_number,l_deal_type
1350: ,l_trans_mts,l_settle_by,l_audit_indicator;
1351: if(l_deal_type = 'RTMM') then
1352: close cur_settle_detail;
1353: update XTR_DEAL_DATE_AMOUNTS_V
1354: set SETTLEMENT_NUMBER = l_settle_number,
1355: ACTUAL_SETTLEMENT_DATE = l_settle_date,
1356: TRANS_MTS = l_trans_mts,
1357: SETTLEMENT_AUTHORISED_BY = l_settle_by,

Line 1372: delete from XTR_DEAL_DATE_AMOUNTS_V

1368: End if;
1369: --
1370: end if;
1371: if nvl(pmt.PRINCIPAL_ADJUST,0) = 0 then
1372: delete from XTR_DEAL_DATE_AMOUNTS_V
1373: where DEAL_NUMBER = l_deal_no
1374: and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1375: and AMOUNT_TYPE in ('PRINFLW')
1376: and ACTION_CODE = 'INCRSE';

Line 1396: UPDATE XTR_DEAL_DATE_AMOUNTS_V

1392: End if;
1393: end if;
1394:
1395: -- Update BALOUT Amounts
1396: UPDATE XTR_DEAL_DATE_AMOUNTS_V
1397: set AMOUNT = nvl(pmt.BALANCE_OUT,0),
1398: HCE_AMOUNT = hce_balos
1399: where DEAL_NUMBER = l_deal_no
1400: and DEAL_TYPE='RTMM'

Line 1409: insert into XTR_DEAL_DATE_AMOUNTS_V

1405:
1406: if SQL%NOTFOUND then
1407: -- Add 1 more row to DDA for Balout
1408:
1409: insert into XTR_DEAL_DATE_AMOUNTS_V
1410: (deal_type,amount_type,date_type,
1411: deal_number,transaction_number,transaction_date,currency,
1412: amount,hce_amount,amount_date,transaction_rate,
1413: cashflow_amount,company_code,account_no,action_code,

Line 1527: select XTR_DEAL_DATE_AMOUNTS_S.NEXTVAL

1523: rec_det REC%ROWTYPE;
1524: v_netoff_number NUMBER;
1525: --
1526: cursor REC_NUM is
1527: select XTR_DEAL_DATE_AMOUNTS_S.NEXTVAL
1528: from DUAL;
1529: --
1530:
1531: Cursor C1(p_netoff_number number) is

Line 1535: from xtr_deal_date_amounts

1531: Cursor C1(p_netoff_number number) is
1532: select net_id from xtr_settlement_summary
1533: where settlement_number =
1534: (select settlement_number
1535: from xtr_deal_date_amounts
1536: where netoff_number = p_netoff_number
1537: and rownum < 2);
1538:
1539: Cursor C2(p_reconciled_reference number) is

Line 1541: from xtr_deal_date_amounts

1537: and rownum < 2);
1538:
1539: Cursor C2(p_reconciled_reference number) is
1540: select settlement_number
1541: from xtr_deal_date_amounts
1542: where reconciled_reference = p_reconciled_reference;
1543:
1544: /************* CE Reconc project ******************/
1545: /* modified the cursor to differentiate non-netted records from netted records. incase of netted records, we need to

Line 1549: from XTR_DEAL_DATE_AMOUNTS_V

1545: /* modified the cursor to differentiate non-netted records from netted records. incase of netted records, we need to
1546: look at the netted amount. */
1547: cursor DDA is
1548: select sum(round(CASHFLOW_AMOUNT,2)),count(*),AMOUNT_DATE, NETOFF_NUMBER
1549: from XTR_DEAL_DATE_AMOUNTS_V
1550: where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1551: and nvl(l_deal_no,'N') = 'Y')
1552: or (l_deal_no is NULL )) --- modify
1553: and ((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and

Line 1584: from XTR_DEAL_DATE_AMOUNTS_V

1580: and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N') ='Y')
1581: group by AMOUNT_DATE, NETOFF_NUMBER
1582: union all
1583: select sum(round(CASHFLOW_AMOUNT,2)),count(distinct NETOFF_NUMBER),AMOUNT_DATE, NETOFF_NUMBER
1584: from XTR_DEAL_DATE_AMOUNTS_V
1585: where
1586: /*
1587: ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1588: and nvl(l_deal_no,'N') = 'Y')

Line 1627: from XTR_DEAL_DATE_AMOUNTS_V

1623: /***************** CE Recon project **********************/
1624: /* non-netted records are summed up, while netted records are summed by the netoff_number */
1625: cursor DDA_SUM_DATE is
1626: select AMOUNT_DATE,sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(NETOFF_NUMBER, -1)), NETOFF_NUMBER
1627: from XTR_DEAL_DATE_AMOUNTS_V
1628: where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1629: and nvl(l_deal_no,'N') = 'Y')
1630: or (l_deal_no is NULL and date_type <> 'FORCAST'))
1631: and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and

Line 1658: from XTR_DEAL_DATE_AMOUNTS_V

1654: /************* CE Recon proejct *************/
1655: /* netted records and non-netted records are dealt with separately*/
1656: cursor DDA_SUM_RANGE is
1657: select sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(netoff_number, -1)), Netoff_number
1658: from XTR_DEAL_DATE_AMOUNTS_V
1659: where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1660: and nvl(l_deal_no,'N') = 'Y')
1661: or (l_deal_no is NULL and date_type<>'FORCAST'))
1662: and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and

Line 1895: update XTR_DEAL_DATE_AMOUNTS

1891: utl_file.put_line (l_file,'>>---- inside IF ');
1892: utl_file.put_line (l_file,'>>------------------------------');
1893: /***************/
1894:
1895: update XTR_DEAL_DATE_AMOUNTS
1896: set RECONCILED_REFERENCE = l_rec_nos,
1897: RECONCILED_PASS_CODE =
1898: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
1899: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 1956: update XTR_DEAL_DATE_AMOUNTS

1952: utl_file.put_line (l_file,'>>---- inside ELSE ');
1953: utl_file.put_line (l_file,'>>------------------------------');
1954: /***************/
1955:
1956: update XTR_DEAL_DATE_AMOUNTS
1957: set RECONCILED_REFERENCE = l_rec_nos,
1958: RECONCILED_PASS_CODE =
1959: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
1960: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2055: update XTR_DEAL_DATE_AMOUNTS

2051: utl_file.put_line (l_file,'>>---- inside IF '||v_netoff_number);
2052: utl_file.put_line (l_file,'>>------------------------------');
2053: /***************/
2054:
2055: update XTR_DEAL_DATE_AMOUNTS
2056: set RECONCILED_REFERENCE = l_rec_nos,
2057: RECONCILED_PASS_CODE =
2058: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2059: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2117: update XTR_DEAL_DATE_AMOUNTS

2113: utl_file.put_line (l_file,'>>---- inside ELSE ');
2114: utl_file.put_line (l_file,'>>------------------------------');
2115: /***************/
2116:
2117: update XTR_DEAL_DATE_AMOUNTS
2118: set RECONCILED_REFERENCE = l_rec_nos,
2119: RECONCILED_PASS_CODE =
2120: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2121: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2248: update XTR_DEAL_DATE_AMOUNTS

2244: utl_file.put_line (l_file,'>>---- inside IF ');
2245: utl_file.put_line (l_file,'>>------------------------------');
2246: /***************/
2247:
2248: update XTR_DEAL_DATE_AMOUNTS
2249: set RECONCILED_REFERENCE = l_rec_nos,
2250: RECONCILED_PASS_CODE =
2251: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2252: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2311: update XTR_DEAL_DATE_AMOUNTS

2307: utl_file.put_line (l_file,'>>---- inside ELSE net# '||v_netoff_number);
2308: utl_file.put_line (l_file,'>>------------------------------');
2309: /***************/
2310:
2311: update XTR_DEAL_DATE_AMOUNTS
2312: set RECONCILED_REFERENCE = l_rec_nos,
2313: RECONCILED_PASS_CODE =
2314: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2315: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2414: update XTR_DEAL_DATE_AMOUNTS

2410: utl_file.put_line (l_file,'>>----inside IF rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2411: utl_file.put_line (l_file,'>>------------------------------');
2412: /***************/
2413:
2414: update XTR_DEAL_DATE_AMOUNTS
2415: set RECONCILED_REFERENCE = l_rec_nos,
2416: RECONCILED_PASS_CODE =
2417: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2418: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2478: update XTR_DEAL_DATE_AMOUNTS

2474: utl_file.put_line (l_file,'>>---- inside ELSE net#, rec#, rec-pass '||v_netoff_number||' - '||l_rec_nos||' - '||p_pass_code);
2475: utl_file.put_line (l_file,'>>------------------------------');
2476: /***************/
2477:
2478: update XTR_DEAL_DATE_AMOUNTS
2479: set RECONCILED_REFERENCE = l_rec_nos,
2480: RECONCILED_PASS_CODE =
2481: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2482: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2622: update XTR_DEAL_DATE_AMOUNTS

2618: utl_file.put_line (l_file,'>>---- inside IF , rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2619: utl_file.put_line (l_file,'>>------------------------------');
2620: /***************/
2621:
2622: update XTR_DEAL_DATE_AMOUNTS
2623: set RECONCILED_REFERENCE = l_rec_nos,
2624: RECONCILED_PASS_CODE =
2625: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2626: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -

Line 2694: update XTR_DEAL_DATE_AMOUNTS

2690: utl_file.put_line (l_file,'>>---- inside ELSE rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2691: utl_file.put_line (l_file,'>>------------------------------');
2692: /***************/
2693:
2694: update XTR_DEAL_DATE_AMOUNTS
2695: set RECONCILED_REFERENCE = l_rec_nos,
2696: RECONCILED_PASS_CODE =
2697: decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2698: RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -