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.

APPS.XTR_COF_P dependencies on XTR_POSITION_HISTORY

Line 93: L_DEAL_SUBTYPE XTR_POSITION_HISTORY.deal_subtype%TYPE;

89: L_DAILY_INT NUMBER;
90: L_HCE_INT NUMBER;
91: L_NEXT_YEAR DATE;
92:
93: L_DEAL_SUBTYPE XTR_POSITION_HISTORY.deal_subtype%TYPE;
94: L_TRANSACTION_RATE NUMBER;
95: T_AS_AT_DATE DBMS_SQL.DATE_TABLE;
96: T_AS_AT_DATE_INS DBMS_SQL.DATE_TABLE;
97: T_ROWID DBMS_SQL.VARCHAR2_TABLE;

Line 112: from XTR_POSITION_HISTORY

108: V_DEAL_NUMBER NUMBER,
109: V_TRANSACTION_NUMBER NUMBER,
110: V_COMPANY_CODE VARCHAR2) is
111: select rowid, as_at_date
112: from XTR_POSITION_HISTORY
113: where AS_AT_DATE >= V_START_DATE
114: and AS_AT_DATE < V_END_DATE
115: and DEAL_TYPE = V_DEAL_TYPE
116: and DEAL_NUMBER = V_DEAL_NUMBER

Line 130: from XTR_POSITION_HISTORY

126: V_DEAL_TYPE VARCHAR2,
127: V_DEAL_NUMBER NUMBER,
128: V_COMPANY_CODE VARCHAR2) is
129: select rowid, as_at_date
130: from XTR_POSITION_HISTORY
131: where AS_AT_DATE >= V_START_DATE
132: and AS_AT_DATE < V_END_DATE
133: and DEAL_TYPE = V_DEAL_TYPE
134: and DEAL_NUMBER = V_DEAL_NUMBER

Line 147: from XTR_POSITION_HISTORY

143: V_DEAL_TYPE VARCHAR2,
144: V_COMPANY_CODE VARCHAR2,
145: V_ACCOUNT_NO VARCHAR2) is
146: select rowid, as_at_date
147: from XTR_POSITION_HISTORY
148: where AS_AT_DATE >= V_START_DATE
149: and AS_AT_DATE < V_END_DATE
150: and DEAL_TYPE = V_DEAL_TYPE
151: and COMPANY_CODE = V_COMPANY_CODE

Line 164: from XTR_POSITION_HISTORY

160: V_END_DATE DATE,
161: V_DEAL_TYPE VARCHAR2,
162: V_DEAL_NUMBER NUMBER) is
163: select rowid, as_at_date
164: from XTR_POSITION_HISTORY
165: where AS_AT_DATE >= V_START_DATE
166: and AS_AT_DATE < V_END_DATE
167: and DEAL_TYPE = V_DEAL_TYPE
168: and DEAL_NUMBER = V_DEAl_NUMBER

Line 177: The idea is that the correspondence between an IG deal in XTR_POSITION_HISTORY and XTR_COST_OF_FUNDS

173: /*********************************************/
174: /* For deal manager performance issue for IG */
175: /*********************************************/
176: /* This is a pure preformance hack for the IG deal manager
177: The idea is that the correspondence between an IG deal in XTR_POSITION_HISTORY and XTR_COST_OF_FUNDS
178: is one to one. Therefore it is much more effecient to update XTR_COST_OF_FUNDS at the same time
179: we update XTR_POSITION_HISTORY. Block update. However to do this it was necessicary to duplicate
180: some of the logic from maintain_cof procedure. This procedure is for IG deals and only IG deals.
181: In order to use this hack, the developer must adhere to the following requirements and must not

Line 179: we update XTR_POSITION_HISTORY. Block update. However to do this it was necessicary to duplicate

175: /*********************************************/
176: /* This is a pure preformance hack for the IG deal manager
177: The idea is that the correspondence between an IG deal in XTR_POSITION_HISTORY and XTR_COST_OF_FUNDS
178: is one to one. Therefore it is much more effecient to update XTR_COST_OF_FUNDS at the same time
179: we update XTR_POSITION_HISTORY. Block update. However to do this it was necessicary to duplicate
180: some of the logic from maintain_cof procedure. This procedure is for IG deals and only IG deals.
181: In order to use this hack, the developer must adhere to the following requirements and must not
182: deviate or modify them. Be careful when making modifications to any of the affected code areas so
183: as not to violate these assumptions.

Line 189: and UPDATE. For anyone writing SQL upgrade scripts, if data is inserted or updated in XTR_POSITION_HISTORY

185: First, INSERT and UPDATE for IG deals are executed ONLY by the maintain position history procedure.
186: The maintain COF procedure will do no processing for IG deals for INSERT and UPDATE but will continue
187: to be responsible for IG DELETEs. Call MAINTAIN_COF_IG for IG deals and IG deals only. Position
188: History is now responsible for IG data in COF so it must not forget to call this function for INSERT
189: and UPDATE. For anyone writing SQL upgrade scripts, if data is inserted or updated in XTR_POSITION_HISTORY
190: for IG deals then make sure the snapshot of COF is updated appropriately.
191: */
192:
193: procedure maintain_cof_ig is

Line 495: insert into XTR_POSITION_HISTORY(

491: maintain_cof_ig;
492: end if;
493:
494: forall i in 1..T_AS_AT_DATE.COUNT
495: insert into XTR_POSITION_HISTORY(
496: AS_AT_DATE,
497: DEAL_TYPE,
498: DEAL_NUMBER,
499: TRANSACTION_NUMBER,

Line 543: insert into XTR_POSITION_HISTORY(

539:
540: /*
541: WHILE L_AS_AT_DATE < L_END_DATE LOOP
542: -- insert new row
543: insert into XTR_POSITION_HISTORY(
544: AS_AT_DATE,
545: DEAL_TYPE,
546: DEAL_NUMBER,
547: TRANSACTION_NUMBER,

Line 598: delete from XTR_POSITION_HISTORY

594: /* DELETE */
595: /**************/
596: elsif P_ACTION = 'DELETE' then
597: if P_DEAL_TYPE='CA' then
598: delete from XTR_POSITION_HISTORY
599: where DEAL_TYPE='CA'
600: and AS_AT_DATE >= P_START_DATE
601: and ACCOUNT_NO = P_ACCOUNT_NO
602: and COMPANY_CODE = P_COMPANY_CODE;

Line 604: delete from XTR_POSITION_HISTORY

600: and AS_AT_DATE >= P_START_DATE
601: and ACCOUNT_NO = P_ACCOUNT_NO
602: and COMPANY_CODE = P_COMPANY_CODE;
603: elsif P_DEAL_TYPE='IG' then
604: delete from XTR_POSITION_HISTORY
605: where DEAL_TYPE='IG'
606: and AS_AT_DATE >= P_START_DATE
607: and DEAL_NUMBER = P_DEAL_NUMBER;
608: elsif P_DEAL_TYPE ='ONC' then

Line 611: delete from XTR_POSITION_HISTORY

607: and DEAL_NUMBER = P_DEAL_NUMBER;
608: elsif P_DEAL_TYPE ='ONC' then
609: if P_STATUS_CODE = 'CLOSED' THEN
610: L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
611: delete from XTR_POSITION_HISTORY
612: where AS_AT_DATE >= L_END_DATE
613: and DEAL_TYPE = P_DEAL_TYPE
614: and DEAL_NUMBER = P_DEAL_NUMBER
615: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;

Line 626: update XTR_POSITION_HISTORY

622: fetch CHK_LOCK_ROWS_ONC BULK COLLECT into T_ROWID, T_AS_AT_DATE;
623: close CHK_LOCK_ROWS_ONC;
624:
625: FORALL I in 1..T_ROWID.COUNT
626: update XTR_POSITION_HISTORY
627: set COMPANY_CODE = P_COMPANY_CODE,
628: CPARTY_CODE = P_CPARTY_CODE,
629: DEAL_SUBTYPE = p_deal_subtype,
630: PRODUCT_TYPE = P_PRODUCT_TYPE,

Line 645: delete from XTR_POSITION_HISTORY

641: HCE_INTEREST = l_hce_int
642: where rowid=T_ROWID(I);
643: --
644: else
645: delete from XTR_POSITION_HISTORY
646: where DEAL_NUMBER = P_DEAL_NUMBER
647: and DEAL_TYPE = P_DEAL_TYPE
648: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
649: end if;

Line 652: delete from XTR_POSITION_HISTORY

648: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
649: end if;
650:
651: elsif P_DEAL_TYPE in('TMM','RTMM') then
652: delete from XTR_POSITION_HISTORY
653: where DEAL_NUMBER = P_DEAL_NUMBER
654: and DEAL_TYPE = P_DEAL_TYPE
655: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
656:

Line 660: delete from XTR_POSITION_HISTORY

656:
657: elsif P_DEAL_TYPE ='FX' then
658: if P_STATUS_CODE = 'CLOSED' then
659: L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
660: delete from XTR_POSITION_HISTORY
661: where AS_AT_DATE >= L_END_DATE
662: and DEAL_NUMBER = P_DEAL_NUMBER
663: and DEAL_TYPE = P_DEAL_TYPE
664: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;

Line 666: delete from XTR_POSITION_HISTORY

662: and DEAL_NUMBER = P_DEAL_NUMBER
663: and DEAL_TYPE = P_DEAL_TYPE
664: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
665: else
666: delete from XTR_POSITION_HISTORY
667: where DEAL_NUMBER = P_DEAL_NUMBER
668: and DEAL_TYPE = P_DEAL_TYPE
669: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
670: end if;

Line 674: delete from XTR_POSITION_HISTORY

670: end if;
671: elsif P_DEAL_TYPE ='NI' then
672: if P_STATUS_CODE = 'CLOSED' then
673: L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
674: delete from XTR_POSITION_HISTORY
675: where AS_AT_DATE >= L_END_DATE
676: and DEAL_NUMBER = P_DEAL_NUMBER
677: and DEAL_TYPE = P_DEAL_TYPE
678: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;

Line 680: delete from XTR_POSITION_HISTORY

676: and DEAL_NUMBER = P_DEAL_NUMBER
677: and DEAL_TYPE = P_DEAL_TYPE
678: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
679: else
680: delete from XTR_POSITION_HISTORY
681: where DEAL_TYPE = P_DEAL_TYPE
682: and DEAL_NUMBER = P_DEAL_NUMBER;
683: end if;
684: elsif P_DEAL_TYPE ='BOND' then

Line 687: delete from XTR_POSITION_HISTORY

683: end if;
684: elsif P_DEAL_TYPE ='BOND' then
685: if P_STATUS_CODE = 'CLOSED' then
686: L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
687: delete from XTR_POSITION_HISTORY
688: where AS_AT_DATE >= L_END_DATE
689: and DEAL_TYPE = P_DEAL_TYPE
690: and DEAL_NUMBER = P_DEAL_NUMBER
691: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;

Line 693: delete from XTR_POSITION_HISTORY

689: and DEAL_TYPE = P_DEAL_TYPE
690: and DEAL_NUMBER = P_DEAL_NUMBER
691: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
692: else
693: delete from XTR_POSITION_HISTORY
694: where DEAL_NUMBER = P_DEAL_NUMBER
695: and DEAL_TYPE = P_DEAL_TYPE
696: and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
697: end if;

Line 765: update XTR_POSITION_HISTORY

761: maintain_cof_ig;
762: end if;
763:
764: FORALL I in 1..T_AS_AT_DATE.COUNT
765: update XTR_POSITION_HISTORY
766: set -- COMPANY_CODE removed from update because it cannot change and it was causing an index to be recalculated
767: CPARTY_CODE = P_CPARTY_CODE,
768: DEAL_SUBTYPE = L_DEAL_SUBTYPE,
769: PRODUCT_TYPE = P_PRODUCT_TYPE,

Line 785: insert into XTR_POSITION_HISTORY(

781: where rowid=T_ROWID(I);
782:
783: FORALL I in 1..T_AS_AT_DATE_INS.COUNT
784: -- insert new row
785: insert into XTR_POSITION_HISTORY(
786: AS_AT_DATE,
787: DEAL_TYPE,
788: DEAL_NUMBER,
789: TRANSACTION_NUMBER,

Line 860: update XTR_POSITION_HISTORY

856: close CHK_LOCK_ROWS_IG;
857: end if;
858:
859: if L_ROWID is not null then
860: update XTR_POSITION_HISTORY
861: set COMPANY_CODE = P_COMPANY_CODE,
862: CPARTY_CODE = P_CPARTY_CODE,
863: DEAL_SUBTYPE = decode(DEAL_TYPE,
864: 'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),

Line 896: insert into XTR_POSITION_HISTORY(

892: if P_DEAL_TYPE in('CA','IG') then
893: L_BASE_REF_AMOUNT :=abs(P_BASE_REF_AMOUNT);
894: L_HCE_BASE_REF_AMOUNT :=abs(L_HCE_BASE_REF_AMOUNT);
895: end if;
896: insert into XTR_POSITION_HISTORY(
897: AS_AT_DATE,
898: DEAL_TYPE,
899: DEAL_NUMBER,
900: TRANSACTION_NUMBER,

Line 1057: from XTR_POSITION_HISTORY

1053: cursor GET_PRV_ROWS_ONC(V_DEAL_TYPE VARCHAR2,
1054: V_DEAL_NUMBER NUMBER,
1055: V_TRANSACTION_NUMBER NUMBER) is
1056: select max(AS_AT_DATE + 1)
1057: from XTR_POSITION_HISTORY
1058: where DEAL_TYPE = V_DEAL_TYPE
1059: and DEAL_NUMBER = V_DEAL_NUMBER
1060: and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER;
1061:

Line 1068: from XTR_POSITION_HISTORY

1064: V_DEAL_TYPE VARCHAR2,
1065: V_DEAL_NUMBER NUMBER,
1066: V_TRANSACTION_NUMBER NUMBER) is
1067: select rowid
1068: from XTR_POSITION_HISTORY
1069: where AS_AT_DATE = V_AS_AT_DATE
1070: and DEAL_TYPE = V_DEAL_TYPE
1071: and DEAL_NUMBER = V_DEAL_NUMBER
1072: and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER

Line 1081: from XTR_POSITION_HISTORY

1077: /*************************/
1078: cursor GET_PRV_ROWS_BOND(V_DEAL_TYPE VARCHAR2,
1079: V_DEAL_NUMBER NUMBER) is
1080: select max(AS_AT_DATE + 1)
1081: from XTR_POSITION_HISTORY
1082: where DEAL_TYPE = V_DEAL_TYPE
1083: and DEAL_NUMBER = V_DEAL_NUMBER;
1084:
1085: cursor BOND_LAST_PROC_DATE(V_AS_AT_DATE DATE,

Line 1098: from XTR_POSITION_HISTORY

1094: cursor CHK_LOCK_ROWS_BOND(V_AS_AT_DATE DATE,
1095: V_DEAL_TYPE VARCHAR2,
1096: V_DEAL_NUMBER NUMBER) is
1097: select rowid
1098: from XTR_POSITION_HISTORY
1099: where AS_AT_DATE = V_AS_AT_DATE
1100: and DEAL_TYPE = V_DEAL_TYPE
1101: and DEAL_NUMBER = V_DEAL_NUMBER
1102: for update of BASE_REF_AMOUNT NOWAIT;

Line 1111: from XTR_POSITION_HISTORY

1107: /***********************************************/
1108: cursor GET_PRV_ROWS_TMM(V_DEAL_TYPE VARCHAR2,
1109: V_DEAL_NUMBER NUMBER) is
1110: select max(AS_AT_DATE + 1)
1111: from XTR_POSITION_HISTORY
1112: where DEAL_TYPE = V_DEAL_TYPE
1113: and DEAL_NUMBER = V_DEAL_NUMBER;
1114:
1115: cursor CHK_LOCK_ROWS_TMM(V_AS_AT_DATE DATE,

Line 1119: from XTR_POSITION_HISTORY

1115: cursor CHK_LOCK_ROWS_TMM(V_AS_AT_DATE DATE,
1116: V_DEAL_TYPE VARCHAR2,
1117: V_DEAL_NUMBER NUMBER) is
1118: select rowid
1119: from XTR_POSITION_HISTORY
1120: where AS_AT_DATE = V_AS_AT_DATE
1121: and DEAL_TYPE = V_DEAL_TYPE
1122: and DEAL_NUMBER = V_DEAL_NUMBER
1123: for update of BASE_REF_AMOUNT NOWAIT;

Line 1132: from XTR_POSITION_HISTORY

1128: cursor GET_PRV_ROWS_CA(V_DEAL_TYPE VARCHAR2,
1129: V_COMPANY_CODE VARCHAR2,
1130: V_ACCOUNT_NO VARCHAR2) is
1131: select max(AS_AT_DATE + 1)
1132: from XTR_POSITION_HISTORY
1133: where DEAL_TYPE = V_DEAl_TYPE
1134: and COMPANY_CODE = V_COMPANY_CODE
1135: and ACCOUNT_NO = V_ACCOUNT_NO;
1136:

Line 1142: from XTR_POSITION_HISTORY

1138: V_DEAL_TYPE VARCHAR2,
1139: V_COMPANY_CODE VARCHAR2,
1140: V_ACCOUNT_NO VARCHAR2) is
1141: select rowid
1142: from XTR_POSITION_HISTORY
1143: where AS_AT_DATE = V_AS_AT_DATE
1144: and DEAL_TYPE = V_DEAL_TYPE
1145: and COMPANY_CODE = V_COMPANY_CODE
1146: and ACCOUNT_NO = V_ACCOUNT_NO

Line 1155: from XTR_POSITION_HISTORY

1151: /***********************/
1152: cursor GET_PRV_ROWS_IG(V_DEAL_TYPE VARCHAR2,
1153: V_DEAL_NUMBER NUMBER) is
1154: select MAX(AS_AT_DATE + 1)
1155: from XTR_POSITION_HISTORY
1156: where DEAL_TYPE = V_DEAL_TYPE
1157: and DEAL_NUMBER = V_DEAl_NUMBER;
1158:
1159: cursor CHK_LOCK_ROWS_IG(V_AS_AT_DATE DATE,

Line 1163: from XTR_POSITION_HISTORY

1159: cursor CHK_LOCK_ROWS_IG(V_AS_AT_DATE DATE,
1160: V_DEAL_TYPE VARCHAR2,
1161: V_DEAL_NUMBER NUMBER) is
1162: select rowid
1163: from XTR_POSITION_HISTORY
1164: where AS_AT_DATE = V_AS_AT_DATE
1165: and DEAL_TYPE = V_DEAL_TYPE
1166: and DEAL_NUMBER = V_DEAl_NUMBER
1167: for update of BASE_REF_AMOUNT NOWAIT;

Line 1326: Delete from XTR_POSITION_HISTORY

1322: Open get_bond_resale;
1323: Fetch get_bond_resale into l_face_value_sold, l_last_resale_date;
1324: If get_bond_resale%FOUND then
1325: If nvl(l_face_value_sold, 0) = P_MATURITY_AMOUNT then -- totally resale
1326: Delete from XTR_POSITION_HISTORY
1327: Where deal_number = P_DEAL_NUMBER
1328: And as_at_date >= l_last_resale_date;
1329:
1330: close get_bond_resale;

Line 1478: update XTR_POSITION_HISTORY

1474: /*========================================*/
1475: /* Insert or Update postion history table */
1476: /*========================================*/
1477: if L_ROWID is not null then
1478: update XTR_POSITION_HISTORY
1479: set COMPANY_CODE = P_COMPANY_CODE,
1480: CPARTY_CODE = P_CPARTY_CODE,
1481: /* DEAL_SUBTYPE = decode(DEAL_TYPE,
1482: 'CA',decode(sign(nvl(BASE_REF_AMOUNT,0)

Line 1518: insert into XTR_POSITION_HISTORY(

1514: L_HCE_INT)
1515: where rowid=l_rowid;
1516: else
1517: -- insert new row
1518: insert into XTR_POSITION_HISTORY(
1519: AS_AT_DATE,
1520: DEAL_TYPE,
1521: DEAL_NUMBER,
1522: TRANSACTION_NUMBER,

Line 3226: FROM xtr_position_history

3222: ----
3223: -- Find min and max trans rates for INVEST deals queried with the given criteria
3224: CURSOR c_get_minmax_rate_invest (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3225: SELECT Min(transaction_rate), Max(transaction_rate)
3226: FROM xtr_position_history
3227: WHERE as_at_date BETWEEN p_from_date and p_to_date
3228: AND ((v_company IS NOT NULL AND company_code = v_company)
3229: OR (v_company IS NULL AND
3230: company_code IN (SELECT p.party_code

Line 3245: FROM xtr_position_history

3241: --
3242: -- Find min and max trans rates for FUND deals queried with the given criteria
3243: CURSOR c_get_minmax_rate_fund (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3244: SELECT Min(transaction_rate), Max(transaction_rate)
3245: FROM xtr_position_history
3246: WHERE as_at_date BETWEEN p_from_date and p_to_date
3247: AND ((v_company IS NOT NULL AND company_code = v_company)
3248: OR (v_company IS NULL AND
3249: company_code IN (SELECT p.party_code

Line 3263: FROM xtr_position_history

3259: --
3260: -- Find min and max trans rates for FX deals queried with the given criteria
3261: CURSOR c_get_minmax_rate_fx (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3262: SELECT Min(transaction_rate), Max(transaction_rate)
3263: FROM xtr_position_history
3264: WHERE as_at_date BETWEEN p_from_date and p_to_date
3265: AND ((v_company IS NOT NULL AND company_code = v_company)
3266: OR (v_company IS NULL AND
3267: company_code IN (SELECT p.party_code