[Home] [Help]
PACKAGE BODY: APPS.CS_FETCH_ARTXN_INFO_PKG
Source
1 PACKAGE BODY CS_FETCH_ARTXN_INFO_PKG AS
2 /* $Header: csctfchb.pls 115.3 99/07/16 08:52:24 porting ship $ */
3 PROCEDURE fetch_trx_information (
4 ERRBUF OUT VARCHAR2,
5 RETCODE OUT NUMBER
6 ) IS
7
8 CURSOR cur_contracts_billing IS
9 SELECT *
10 FROM CS_CONTRACTS_BILLING
11 WHERE trx_number is NULL
12 FOR UPDATE OF contract_billing_id ;
13
14 cur_record cur_contracts_billing%ROWTYPE;
15 conc_status BOOLEAN;
16 v_retcode NUMBER;
17 trx_class VARCHAR2(20);
18 tax_amount NUMBER;
19 trx_type_id NUMBER;
20 trx_number NUMBER;
21 trx_date DATE;
22 trx_amount NUMBER;
23 temp_cp_service_trx_id NUMBER;
24 v_return_status VARCHAR2(1);
25 v_msg_count NUMBER;
26 v_msg_data VARCHAR2(2000);
27
28 SUCCESS NUMBER := 0;
29 ERROR NUMBER := 1;
30 ret_status NUMBER;
31 BEGIN
32
33 -- FND_GLOBAL.APPS_INITIALIZE(1001,170,20638);
34
35 -- FND_FILE.PUT_NAMES('bk.log','bk.out','/sqlcom/log');
36
37 OPEN cur_contracts_billing;
38 LOOP
39 FETCH cur_contracts_billing INTO cur_record;
40 EXIT WHEN cur_contracts_billing%NOTFOUND;
41
42 BEGIN
43 SELECT
44 lines.interface_line_attribute10,
45 trx.cust_trx_type_id,
46 trx.trx_number,
47 lines.extended_amount,
48 trtypes.type,
49 trx.trx_date
50 INTO
51 temp_cp_service_trx_id,
52 trx_type_id,
53 trx_number,
54 trx_amount,
55 trx_class,
56 trx_date
57 FROM ra_customer_trx trx,
58 ra_customer_trx_lines lines,
59 ra_cust_trx_types trtypes
60 WHERE lines.line_type='LINE'
61 AND lines.interface_line_attribute1=to_char(cur_record.contract_id)
62 AND lines.interface_line_attribute2=to_char(cur_record.cp_service_id)
63 AND lines.interface_line_attribute4=to_char(cur_record.contract_billing_id)
64 AND trx.customer_trx_id=lines.customer_trx_id
65 AND trtypes.cust_trx_type_id=trx.cust_trx_type_id ;
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 ret_status := ERROR;
70 FND_FILE.PUT_LINE(FND_FILE.LOG,'No line in ra_customer_trx_lines for Contract ID : ' ||
71 to_char(cur_record.contract_id));
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'SErvice ID : ' ||
73 to_char(cur_record.cp_service_id));
74 FND_FILE.PUT_LINE(FND_FILE.LOG,'Contract_Billing ID : ' ||
75 to_char(cur_record.contract_billing_id));
76
77 END;
78
79 IF (ret_status = ERROR ) THEN
80 ret_status := SUCCESS;
81 ELSE
82 BEGIN
83 SELECT extended_amount
84 INTO tax_amount
85 FROM ra_customer_trx_lines
86 WHERE line_type='TAX'
87 AND interface_line_attribute1=to_char(cur_record.contract_id)
88 AND interface_line_attribute2=to_char(cur_record.cp_service_id)
89 AND interface_line_attribute4=to_char(cur_record.contract_billing_id);
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 tax_amount := 0;
93 FND_FILE.PUT_LINE(FND_FILE.LOG,'No tax amount for Contract ID : ' ||
94 to_char(cur_record.contract_id));
95 FND_FILE.PUT_LINE(FND_FILE.LOG,'SErvice ID : ' ||
96 to_char(cur_record.cp_service_id));
97 FND_FILE.PUT_LINE(FND_FILE.LOG,'Contract_Billing ID : ' ||
98 to_char(cur_record.contract_billing_id));
99
100 END;
101
102 --call API to write invoice details
103
104 CS_CONBILLING_PVT.Update_Billing(
105 p_api_version => 1.0,
106 p_init_msg_list => 'T',
107 p_commit => 'F',
108 p_cp_service_trx_id => temp_cp_service_trx_id,
109 p_contract_id => cur_record.contract_id,
110 p_trx_type_id => trx_type_id,
111 p_trx_number => trx_number,
112 p_trx_date => trx_date,
113 p_trx_pre_tax_amount => trx_amount,
114 p_tot_trx_amount => trx_amount+tax_amount,
115 p_contract_billing_id => cur_record.contract_billing_id,
116 p_obj_version_number => cur_record.object_version_number,
117 x_return_status => v_return_status,
118 x_msg_count => v_msg_count,
119 x_msg_data => v_msg_data);
120
121
122 IF (v_return_status <>'S'and v_msg_count>=1) THEN
123 FND_FILE.PUT_LINE(FND_FILE.LOG,
124 'Error in updating contracts billing');
125 FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE : ' ||v_msg_data);
126 ELSE
127 FND_FILE.PUT_LINE(FND_FILE.LOG,
128 'Successfully updated contracts billing');
129 COMMIT;
130 END IF ;
131
132 END IF;
133
134
135 END LOOP;
136 CLOSE cur_contracts_billing;
137
138 COMMIT;
139 IF v_return_status = 'S' THEN
140 conc_status:=
141 FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',v_return_status);
142 ELSE
143 conc_status:=
144 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',v_return_status);
145 END IF;
146
147 --FND_FILE.CLOSE;
148 EXCEPTION
149 WHEN OTHERS THEN
150 CLOSE cur_contracts_billing;
151 v_msg_data := fnd_message.get;
152 FND_FILE.PUT_LINE(FND_FILE.LOG,
153 'Exception in fetch_trx_information :'||v_msg_data);
154 --IF (v_return_status <>'S'and v_msg_count>=1) THEN
155 -- FND_FILE.PUT_LINE(FND_FILE.LOG,
156 -- 'Exception in fetch_trx_information :'||sqlerrm);
157 --END IF ;
158
159 END fetch_trx_information;
160
161 END CS_FETCH_ARTXN_INFO_PKG;