[Home] [Help]
PACKAGE BODY: APPS.CE_CP_OA_XTR_PKG
Source
1 PACKAGE BODY CE_CP_OA_XTR_PKG AS
2 /* $Header: cecputlb.pls 120.3 2006/01/31 11:19:19 svali ship $ */
3
4 FUNCTION FIND_COMPANY_CODE(X_bank_account_id NUMBER) RETURN VARCHAR2 IS
5 result XTR_BANK_ACCOUNTS.party_code%TYPE;
6 BEGIN
7
8 BEGIN
9 SELECT party_code
10 INTO result
11 FROM XTR_BANK_ACCOUNTS
12 WHERE nvl(ap_bank_account_id, dummy_bank_account_id) = X_bank_account_id;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 result := null;
16 END;
17
18 return result;
19
20 END FIND_COMPANY_CODE;
21
22 PROCEDURE XTR_GEN_EXPOSURES(
23 X_bank_account_id IN VARCHAR2,
24 X_account_number IN VARCHAR2,
25 X_amount IN VARCHAR2,
26 X_base_amount IN VARCHAR2,
27 X_currency_code IN VARCHAR2,
28 X_rate IN VARCHAR2,
29 X_exposure_type IN VARCHAR2,
30 X_portfolio_code IN VARCHAR2,
31 X_trx_date IN DATE,
32 X_comments IN VARCHAR2,
33 X_user_id IN VARCHAR2) IS
34 rec XTR_EXPOSURE_TRANSACTIONS%rowtype;
35 error1 boolean;
36 error2 boolean;
37 error3 boolean;
38 error4 boolean;
39 BEGIN
40 rec.account_no := X_account_number;
41 rec.amount := to_number(X_amount);
42 if (rec.amount >= 0) then
43 rec.action_code := 'REC';
44 else
45 rec.action_code := 'PAY';
46 end if;
47 rec.amount := abs(rec.amount);
48 rec.amount_hce := to_number(X_base_amount);
49 rec.currency := X_currency_code;
50 if (X_rate is not null) then
51 rec.avg_rate := to_number(X_rate);
52 end if;
53 rec.company_code := find_company_code(to_number(X_bank_account_id));
54 rec.created_by := to_number(X_user_id);
55 rec.created_on := sysdate;
56 rec.deal_type := 'EXP';
57 rec.deal_subtype := 'FIRM';
58 rec.exposure_type := X_exposure_type;
59 rec.portfolio_code := X_portfolio_code;
60 rec.settle_action_reqd := 'N';
61 rec.value_date := X_trx_date;
62 rec.internal_comments := X_comments;
63 rec.purchasing_module := 'N';
64 rec.cash_position_exposure := 'Y';
65
66 XTR_EXP_TRANSFERS_PKG.transfer_exp_deals(rec, 'FORM', error1, error2, error3,
67 error4);
68
69 END XTR_GEN_EXPOSURES;
70
71 FUNCTION INCLUDE_INDIC(
72 X_WS_ID IN NUMBER,
73 X_SRC_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
74 l_indic VARCHAR2(1);
75 BEGIN
76
77 BEGIN
78 SELECT indicative_flag
79 INTO l_indic
80 FROM CE_CP_WORKSHEET_LINES
81 WHERE worksheet_header_id = X_WS_ID
82 AND source_type = X_SRC_TYPE
83 AND (trx_type is null OR trx_type = 'EXP');
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 l_indic := 'N';
87 WHEN OTHERS THEN
88 NULL;
89 END;
90
91 RETURN l_indic;
92
93 END INCLUDE_INDIC;
94
95 PROCEDURE SUBMIT_GEN_PRIOR_DAY(
96 X_WS_ID IN NUMBER,
97 X_AS_OF_DATE IN VARCHAR2) IS
98 l_request_id NUMBER;
99 BEGIN
100
101 l_request_id := FND_REQUEST.submit_request(
102 'CE', 'CECPPRIB', '', '', NULL,
103 to_char(X_WS_ID), X_AS_OF_DATE, 'N', '', '',
104 FND_GLOBAL.local_chr(0), '', '', '', '',
105 '','','','','','','','','','',
106 '','','','','','','','','','',
107 '','','','','','','','','','',
108 '','','','','','','','','','',
109 '','','','','','','','','','',
110 '','','','','','','','','','',
111 '','','','','','','','','','',
112 '','','','','','','','','','',
113 '','','','','','','','','','');
114
115 COMMIT;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 cep_standard.debug('EXCEPTION: CE_CP_OA_XTR_PKG.submit_gen_prior_day');
120 RAISE;
121
122 END SUBMIT_GEN_PRIOR_DAY;
123
124
125 PROCEDURE update_projected_balances(
126 p_bank_account_id NUMBER,
127 p_balance_date DATE,
128 p_balance_amount NUMBER)
129 IS
130 BEGIN
131 DELETE FROM ce_projected_balances
132 WHERE bank_account_id=p_bank_account_id
133 AND balance_date=p_balance_date;
134
135 INSERT INTO ce_projected_balances
136 (projected_balance_id,
137 bank_Account_id,
138 balance_date,
139 projected_balance,
140 last_update_date,
141 last_updated_by,
142 creation_date,
143 created_by,
144 last_update_login,
145 object_version_number)
146 VALUES (ce_projected_Balances_s.nextval,
147 p_bank_account_id,
148 p_balance_date,
149 p_balance_amount,
150 sysdate,
151 NVL(FND_GLOBAL.user_id,-1),
152 sysdate,
153 NVL(FND_GLOBAL.user_id,-1),
154 NVL(FND_GLOBAL.user_id,-1),
155 1);
156
157 END update_projected_balances;
158
159 END CE_CP_OA_XTR_PKG;