1 PACKAGE BODY XTR_CONFO_PROCESS_P as
2 /* $Header: xtrconfb.pls 120.1 2005/06/29 12:42:47 badiredd ship $ */
3 ---------------------------------------------------------------------------------------------------------------------------
4 PROCEDURE POST_CONFIRMATION_DETAILS(p_deal_no IN NUMBER,
5 p_trans_no IN NUMBER,
6 p_action IN VARCHAR2,
7 p_cparty IN VARCHAR2,
8 p_client IN VARCHAR2,
9 p_company_code IN VARCHAR2,
10 p_confo_party_code IN VARCHAR2,
11 p_deal_type IN VARCHAR2,
12 p_deal_subtype IN VARCHAR2,
13 p_currency IN VARCHAR2,
14 p_amount IN NUMBER,
15 p_deal_status IN VARCHAR2) is
16 --
17 l_party VARCHAR2(7);
18 l_action NUMBER;
19 --
20 cursor GET_CONFO_ACTIONS is
21 select 1
22 from XTR_CONFIRMATION_ACTIONS c,
23 XTR_PARTIES_V p
24 where p.PARTY_CODE = l_party
25 and c.CONFO_ACTION_GROUP = p.CONFO_ACTION_CODE
26 and c.DEAL_TYPE = p_deal_type
27 and c.ACTION_TYPE = p_action;
28 --
29 begin
30 null; -- ************ Check if this procedure is reqd any longer (replaced by trigger - refer John)
31 /*
32 -- ***** Post Confirmation row for COUNTERPARTY confirmation *****
33 if p_cparty is NOT NULL then
34 l_party := p_cparty;
35 update XTR_CONFIRMATION_DETAILS
36 set DEAL_SUBTYPE = p_deal_subtype,
37 CURRENCY = p_currency,
38 AMOUNT = p_amount,
39 CONFO_PARTY_CODE = l_party
40 where DEAL_NO = p_deal_no
41 and TRANSACTION_NO = p_trans_no
42 and CLIENT_OR_CPARTY = 'CP';
43 --
44 if SQL%NOTFOUND then
45 open GET_CONFO_ACTIONS;
46 fetch GET_CONFO_ACTIONS into l_action;
47 if GET_CONFO_ACTIONS%FOUND then
48 -- Insert row into Confirmation_details so confo can be generated
49 insert into XTR_CONFIRMATION_DETAILS
50 (deal_no,transaction_no,action_type,client_or_cparty,date_created,
51 company_code,confo_party_code,file_name,deal_type,
52 deal_subtype,currency,amount,confirmation_validated_by,
53 confirmation_validated_on,status_code)
54 values
55 (p_deal_no,p_trans_no,p_action,'CP',sysdate,p_company_code,
56 l_party,NULL,p_deal_type,p_deal_subtype,p_currency,p_amount,
57 NULL,NULL,p_deal_status);
58 end if;
59 close GET_CONFO_ACTIONS;
60 end if;
61 end if;
62 --
63 -- ***** Post Confirmation row for CLIENT confirmation *****
64 if p_client is NOT NULL then
65 l_party := p_client;
66 update XTR_CONFIRMATION_DETAILS
67 set DEAL_SUBTYPE = p_deal_subtype,
68 STATUS_CODE = p_deal_status,
69 CURRENCY = p_currency,
70 AMOUNT = p_amount,
71 CONFO_PARTY_CODE = l_party
72 where DEAL_NO = p_deal_no
73 and TRANSACTION_NO = p_trans_no
74 and CLIENT_OR_CPARTY = 'CL';
75 if SQL%NOTFOUND then
76 open GET_CONFO_ACTIONS;
77 fetch GET_CONFO_ACTIONS into l_action;
78 if GET_CONFO_ACTIONS%FOUND then
79 -- Insert row into Confirmation_details so confo can be generated
80 insert into XTR_CONFIRMATION_DETAILS
81 (deal_no,transaction_no,action_type,client_or_cparty,date_created,
82 company_code,confo_party_code,file_name,deal_type,
83 deal_subtype,currency,amount,confirmation_validated_by,
84 confirmation_validated_on,status_code)
85 values
86 (p_deal_no,p_trans_no,p_action,'CL',sysdate,p_company_code,
87 l_party,NULL,p_deal_type,p_deal_subtype,p_currency,p_amount,
88 NULL,NULL,p_deal_status);
89 end if;
90 close GET_CONFO_ACTIONS;
91 end if;
92 end if;
93 */
94 end POST_CONFIRMATION_DETAILS;
95 ---------------------------------------------------------------------------------------------------------------------------
96 PROCEDURE CANCEL_CONFO(p_deal_no IN NUMBER,
97 p_trans_no IN NUMBER) is
98 --
99 begin
100 delete from XTR_CONFIRMATION_DETAILS
101 where DEAL_NO = p_deal_no
102 and TRANSACTION_NO = nvl(p_trans_no,p_deal_no)
103 and FILE_NAME is NULL;
104 --
105 update XTR_CONFIRMATION_DETAILS
106 set STATUS_CODE = 'CANCELLED'
107 where DEAL_NO = p_deal_no
108 and TRANSACTION_NO = p_trans_no
109 and FILE_NAME is NOT NULL;
110 --
111 end CANCEL_CONFO;
112
113 ---------------------------------------------------------------------------------------------------------------------------
114
115 PROCEDURE CALL_XTRLTCFM (P_TEMPLATE_TYPE IN VARCHAR2,
116 P_TEMPLATE_NAME IN VARCHAR2,
117 P_CREATED_ON IN DATE,
118 P_EFFECTIVE_DATE IN DATE,
119 P_DEAL_NO IN NUMBER,
120 P_PRODUCT_TYPE IN VARCHAR2,
121 P_PAYMENT_SCHEDULE IN VARCHAR2,
122 P_TOTAL OUT NOCOPY NUMBER,
123 P_SUCCESS OUT NOCOPY NUMBER) is
124 --
125 l_deal_no XTR_DEALS_V.DEAL_NO%TYPE;
126 l_deal_type XTR_DEALS_V.DEAL_TYPE%TYPE;
127 request_id NUMBER;
128 --
129 cursor GET_TERM_ACTION is
130 select a.DEAL_NO,
131 b.DEAL_TYPE
132 from XTR_TERM_ACTIONS a,
133 XTR_DEALS_V b
134 where ((a.DEAL_NO = P_DEAL_NO and P_DEAL_NO is not null) or (P_DEAL_NO is null))
135 and a.DEAL_NO = b.DEAL_NO
136 and a.CREATED_ON = P_CREATED_ON
137 and ((P_TEMPLATE_TYPE = 'RETAIL TERM CPARTY PRIN ADJUST' and
138 a.PRINCIPAL_ADJUST is not null and
139 a.INCREASE_EFFECTIVE_FROM_DATE = p_effective_date and
140 b.PAYMENT_SCHEDULE_CODE = nvl(P_PAYMENT_SCHEDULE,b.PAYMENT_SCHEDULE_CODE) and
141 b.PRODUCT_TYPE = nvl(P_PRODUCT_TYPE,b.PRODUCT_TYPE))
142 or (P_TEMPLATE_TYPE = 'RETAIL TERM CPARTY SCHEDULE' and
143 a.PAYMENT_SCHEDULE_CODE = nvl(P_PAYMENT_SCHEDULE,a.PAYMENT_SCHEDULE_CODE) and
144 a.FROM_START_DATE = p_effective_date and
145 b.PRODUCT_TYPE = nvl(P_PRODUCT_TYPE,b.PRODUCT_TYPE))
146 or (P_TEMPLATE_TYPE = 'RETAIL TERM CPARTY INT RESET' and
147 a.NEW_INTEREST_RATE is not null and
148 a.EFFECTIVE_FROM_DATE = p_effective_date and
149 b.PAYMENT_SCHEDULE_CODE = nvl(P_PAYMENT_SCHEDULE,b.PAYMENT_SCHEDULE_CODE) and
150 b.PRODUCT_TYPE = nvl(P_PRODUCT_TYPE,b.PRODUCT_TYPE)))
151 order by a.DEAL_NO;
152
153 --
154 begin
155 --
156 p_total := 0;
157 p_success := 0;
158 --
159 open GET_TERM_ACTION;
160 fetch GET_TERM_ACTION into l_deal_no, l_deal_type;
161 while GET_TERM_ACTION%FOUND loop
162 p_total := p_total + 1;
163 request_id := FND_REQUEST.SUBMIT_REQUEST(
164 'XTR', 'XTRLTCFM','','',FALSE,
165 'N', -- example
166 'N', -- SQL trace
167 'N', -- display debug
168 to_char(l_deal_no),
169 null, -- Name_In('C.transaction_no'),
170 l_deal_type,
171 null, -- Name_In('C.confo_party_code'),
172 null, -- Name_In('C.action_type'),
173 p_template_type,
174 to_char(p_created_on, 'YYYY/MM/DD HH24:MI:SS'),
175 chr(0),
176 '','','','','','','','','',
177 '','','','','','','','','','',
178 '','','','','','','','','','',
179 '','','','','','','','','','',
180 '','','','','','','','','','',
181 '','','','','','','','','','',
182 '','','','','','','','','','',
183 '','','','','','','','','','',
184 '','','','','','','','','','');
185 If (request_id <> 0) then
186 p_success := p_success + 1;
187 End If;
188 fetch GET_TERM_ACTION into l_deal_no, l_deal_type;
189 End Loop;
190 if p_total = 1 then
191 p_success := request_id;
192 end if;
193 close GET_TERM_ACTION;
194 commit;
195 end CALL_XTRLTCFM;
196 ---------------------------------------------------------------------------------------------------------------------------
197 end XTR_CONFO_PROCESS_P;
[Home] [Help]