1 PACKAGE BODY CS_CP_Termination_PKG AS
2 /* $Header: csxcpstb.pls 115.0 2000/02/01 16:34:22 pkm ship $ */
3
4 --
5 -- Constant values
6 G_DEBUG CONSTANT BOOLEAN := FALSE; -- Run engine in debug or normal mode
7
8 --
9 -- Global variables
10 g_default_term_status_id NUMBER;
11
12 /*******************************
13 * Public program units *
14 *******************************/
15
16 --This is the main procedure that will be called as a conc program
17 PROCEDURE Update_CP_Term_Status
18 (
19 errbuf OUT VARCHAR2,
20 retcode OUT NUMBER
21 ) IS
22 CURSOR cp_term_cur IS
23 SELECT customer_product_id
24 FROM cs_customer_products
25 WHERE trunc(sysdate) >= trunc(nvl(end_date_active, sysdate+1));
26
27 --
28 BEGIN
29 fnd_file.put_line(fnd_file.log, 'Executing pkg body ('||
30 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')||')');
31 --
32 IF (G_DEBUG) THEN
33 fnd_file.put_names('status.log', 'status.out',
34 '/home/sadiga/work/11i/bapi');
35 END IF;
36 --
37 --
38 FOR cp_term_cur_rec IN cp_term_cur LOOP
39 fnd_file.put_line(fnd_file.log, 'Fetched cp_id to terminate '||
40 to_char(cp_term_cur_rec.customer_product_id));
41
42 -- later on, see if we can get the status name instead of printing out
43 -- the status_id.
44
45 fnd_file.put_line(fnd_file.log, 'Updating the status of this product to '||g_default_term_status_id);
46
47 -- see if we call use the Update APis instead.
48
49 UPDATE cs_customer_products
50 SET CUSTOMER_PRODUCT_STATUS_ID = g_default_term_status_id,
51 object_version_number = object_version_number + 1
52 WHERE customer_product_id = cp_term_cur_rec.customer_product_id;
53
54 commit;
55 END LOOP;
56 --
57 --
58 fnd_file.put_line(fnd_file.log, 'Finished execution. Exiting... ('||
59 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')||')');
60 --
61 -- Return 0 for successful completion, 1 for warnings, 2 for error
62 errbuf := '';
63 retcode := 0;
64 --
65 IF (G_DEBUG) THEN
66 fnd_file.close;
67 END IF;
68 --
69 --
70 --
71 EXCEPTION
72 WHEN OTHERS THEN
73 ROLLBACK;
74 IF (G_DEBUG) THEN
75 fnd_file.close;
76 END IF;
77
78 -- Retrieve error message into errbuf
79 errbuf := sqlerrm;
80 retcode := 2;
81 END Update_CP_Term_Status;
82 --
83 --
84 -- Package initialization code. This executes the first time (in a session) any
85 -- thing in the package is referenced.
86 BEGIN
87 g_default_term_status_id := FND_PROFILE.VALUE('CS_IB_DEFAULT_TERMINATED_STATUS');
88 IF g_default_term_status_id IS NULL THEN
89 FND_MESSAGE.SET_NAME('FND', 'PROFILES-CANNOT READ');
90 FND_MESSAGE.SET_TOKEN('OPTION', 'CS_IB_DEFAULT_TERMINATED_STATUS');
91 FND_MESSAGE.SET_TOKEN('ROUTINE',
92 'CS_CP_Termination_PKG.Update_CP_Term_Status');
93 APP_EXCEPTION.RAISE_EXCEPTION;
94 END IF;
95
96 END CS_CP_Termination_PKG;