[Home] [Help]
PACKAGE BODY: APPS.PN_LEASE_DETAILS_SEND_UPD
Source
1 PACKAGE BODY pn_lease_details_send_upd AS
2 -- $Header: PNUPLEDB.pls 120.2 2005/12/01 10:00:53 appldev noship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : UPDATE_LEASE_SE
6 -- INVOKED FROM :
7 -- PURPOSE :
8 -- HISTORY :
9 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_leases with _ALL table.
10 -------------------------------------------------------------------------------
11 PROCEDURE update_lease_se (
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY VARCHAR2,
14 p_lease_class IN VARCHAR2,
15 p_lease_num_from IN VARCHAR2,
16 p_lease_num_to IN VARCHAR2)
17 IS
18 v_getleaseid pn_leases.lease_id%TYPE;
19 s_count NUMBER := 0;
20 l_lease_num pn_leases.lease_num%TYPE;
21 v_batch_size NUMBER := 1000;
22 v_counter NUMBER := 0;
23 l_lastupdatedate DATE :=SYSDATE;
24 l_lastupdatedby NUMBER :=fnd_global.user_id;
25 l_lastupdatelogin NUMBER :=fnd_global.login_id;
26
27 CURSOR c_getleaseid IS
28 SELECT l.lease_id lleaseid,
29 l.lease_class_code,
30 l.name,
31 l.lease_num,
32 d.lease_detail_id,
33 d.lease_change_id,
34 d.lease_id,
35 d.responsible_user dresponsibleeuserid,
36 d.expense_account_id,
37 d.lease_commencement_date,
38 d.lease_termination_date,
39 d.lease_execution_date,
40 d.last_update_date,
41 d.last_updated_by,
42 d.creation_date,
43 d.created_by,
44 d.last_update_login,
45 d.attribute_category,
46 d.attribute1,
47 d.attribute2,
48 d.attribute3,
49 d.attribute4,
50 d.attribute5,
51 d.attribute6,
52 d.attribute7,
53 d.attribute8,
54 d.attribute9,
55 d.attribute10,
56 d.attribute11,
57 d.attribute12,
58 d.attribute13,
59 d.attribute14,
60 d.attribute15,
61 d.accrual_account_id,
62 d.receivable_account_id,
63 d.term_template_id
64 FROM pn_leases l, pn_lease_details_all d
65 WHERE l.lease_id = d.lease_id
66 AND lease_num BETWEEN NVL(p_lease_num_from,lease_num) AND
67 NVL(p_lease_num_to,lease_num)
68 AND lease_class_code = NVL(p_lease_class,lease_class_code)
69 AND l.status = 'F'
70 AND d.send_entries = 'Y';
71 BEGIN
72 pnp_debug_pkg.debug('PN_LEASE_DETAILS_SEND_UPD.UPDATE_LEASE_SE (+)');
73
74 -- Get from dirname from db later,
75 -- and use sequence for unique filenames
76 fnd_file.put_names('PNUPLEDB.log', 'PNUPLEDB.out', '/sqlcom/out');
77
78 v_counter := 0;
79
80 FOR v_lease IN c_getleaseid
81 LOOP
82 pn_lease_details_pkg.update_row
83 (
84 x_lease_detail_id => v_lease.lease_detail_id
85 ,x_lease_change_id => 0
86 ,x_lease_id => v_lease.lease_id
87 ,x_responsible_user => v_lease.dresponsibleeuserid
88 ,x_expense_account_id => v_lease.expense_account_id
89 ,x_lease_commencement_date => v_lease.lease_commencement_date
90 ,x_lease_termination_date => v_lease.lease_termination_date
91 ,x_lease_execution_date => v_lease.lease_execution_date
92 ,x_last_update_date => l_lastUpdateDate
93 ,x_last_updated_by => l_lastupdatedby
94 ,x_last_update_login => l_lastupdatelogin
95 ,x_accrual_account_id => v_lease.accrual_account_id
96 ,x_receivable_account_id => v_lease.receivable_account_id
97 ,x_attribute_category => v_lease.attribute_category
98 ,x_attribute1 => v_lease.attribute1
99 ,x_attribute2 => v_lease.attribute2
100 ,x_attribute3 => v_lease.attribute3
101 ,x_attribute4 => v_lease.attribute4
102 ,x_attribute5 => v_lease.attribute5
103 ,x_attribute6 => v_lease.attribute6
104 ,x_attribute7 => v_lease.attribute7
105 ,x_attribute8 => v_lease.attribute8
106 ,x_attribute9 => v_lease.attribute9
107 ,x_attribute10 => v_lease.attribute10
108 ,x_attribute11 => v_lease.attribute11
109 ,x_attribute12 => v_lease.attribute12
110 ,x_attribute13 => v_lease.attribute13
111 ,x_attribute14 => v_lease.attribute14
112 ,x_attribute15 => v_lease.attribute15
113 ,x_term_template_id => v_lease.term_template_id
114 );
115
116 -------------------------------------------
117 -- For Conc Log/Output files
118 ------------------------------------------
119 fnd_message.set_name ('PN','PN_UPLED_LS_DTLS');
120 fnd_message.set_token ('ID',v_lease.lease_id);
121 fnd_message.set_token ('NUM',v_lease.lease_num);
122 fnd_message.set_token ('CLASS',v_lease.lease_class_code);
123 pnp_debug_pkg.put_log_msg(fnd_message.get);
124 pnp_debug_pkg.put_log_msg (' ');
125
126 s_count := s_count + 1;
127 IF v_counter = v_batch_size THEN
128 COMMIT;
129 v_counter := 0;
130 END IF;
131 END LOOP;
132 COMMIT;
133
134 pnp_debug_pkg.put_log_msg('
135 ================================================================================');
136 fnd_message.set_name ('PN','PN_UPLED_PROC');
137 fnd_message.set_token ('NUM',s_count);
138 pnp_debug_pkg.put_log_msg(fnd_message.get);
139 pnp_debug_pkg.put_log_msg('
140 ================================================================================');
141 pnp_debug_pkg.debug('PN_LEASE_DETAILS_SEND_UPD.update_lease_se (-)');
142 EXCEPTION
143 WHEN OTHERS THEN
144 raise_application_error ('-20001','Error ' || TO_CHAR(sqlcode) );
145 Errbuf := SQLERRM;
146 Retcode := 2;
147 END update_lease_se;
148
149 END pn_lease_details_send_upd;