1 PACKAGE BODY CSC_CUSTOMIZED_PLANS_PKG as
2 /* $Header: csctcupb.pls 115.15 2002/11/25 06:12:33 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_CUSTOMIZED_PLANS_PKG
5 -- Purpose : Table handler package to insert and delete rows in
6 -- CSC_CUSTOMIZED_PLANS table.
7 -- History :
8 -- DD-MM-YYYY NAME MODIFICATIONS
9 -- 11-04-1999 dejoseph Created.
10 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
11 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
12 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
13 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
14 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
15 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
16 -- 04-10-2000 dejoseph Removed reference to cust_account_org in lieu of TCA's
17 -- decision to drop column org_id from hz_cust_accounts.
18 -- 02-07-2001 dejoseph Added parameters to the insert_row procedures to record
19 -- ac/party merge concurrent request information.
20 -- 08-29-2001 dejoseph Removed parameter party_status from procedure insert_row. This
21 -- column does not exist in the database. The merge status will
22 -- be determined from PLAN_STATUS_CODE.
23 -- 11-12-2002 bhroy NOCOPY changes made
24 -- 11-25-2002 bhroy FND_API default changes made, added check file comments and WHENEVER OSERROR EXIT FAILURE ROLLBACK
25 -- NOTE :
26 -- End of Comments
27
28
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_CUSTOMIZED_PLANS_PKG';
30 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctcupb.pls';
31
32 PROCEDURE Insert_Row(
33 px_ID IN OUT NOCOPY NUMBER,
34 p_PLAN_ID IN NUMBER,
35 p_PARTY_ID IN NUMBER,
36 p_cust_account_id IN NUMBER,
37 p_request_id IN NUMBER,
38 p_program_application_id IN NUMBER,
39 p_program_id IN NUMBER,
40 p_program_update_date IN DATE,
41 p_plan_status_code IN VARCHAR2)
42 IS
43 CURSOR C2 IS
44 SELECT CSC_CUSTOMIZED_PLANS_S.nextval
45 FROM sys.dual;
46 BEGIN
47 OPEN C2;
48 FETCH C2 INTO px_ID;
49 CLOSE C2;
50
51 INSERT INTO CSC_CUSTOMIZED_PLANS(
52 ID,
53 PLAN_ID,
54 PARTY_ID ,
55 CUST_ACCOUNT_ID ,
56 REQUEST_ID,
57 PROGRAM_APPLICATION_ID,
58 PROGRAM_ID,
59 PROGRAM_UPDATE_DATE,
60 PLAN_STATUS_CODE )
61 VALUES (
62 px_ID,
63 p_PLAN_ID,
64 p_PARTY_ID,
65 p_cust_account_id,
66 p_request_id,
67 p_program_application_id,
68 p_program_id,
69 p_program_update_date,
70 p_plan_status_code );
71
72 End Insert_Row;
73
74
75 PROCEDURE Delete_Row(
76 P_ID IN NUMBER,
77 P_PLAN_ID IN NUMBER,
78 P_PARTY_ID IN NUMBER)
79 IS
80 BEGIN
81 if ( P_ID IS NOT NULL OR P_ID <> FND_API.G_MISS_NUM ) THEN
82 DELETE FROM CSC_CUSTOMIZED_PLANS
83 WHERE ID = p_ID;
84 elsif ( P_PLAN_ID IS NOT NULL OR P_PLAN_ID <> FND_API.G_MISS_NUM ) THEN
85 IF ( P_PARTY_ID IS NOT NULL OR P_PARTY_ID <> FND_API.G_MISS_NUM ) THEN
86 DELETE FROM CSC_CUSTOMIZED_PLANS
87 WHERE PLAN_ID = P_PLAN_ID
88 AND PARTY_ID = P_PARTY_ID;
89 ELSE
90 DELETE FROM CSC_CUSTOMIZED_PLANS
91 WHERE PLAN_ID = P_PLAN_ID;
92 END IF;
93 else
94 DELETE FROM CSC_CUSTOMIZED_PLANS
95 WHERE PARTY_ID = P_PARTY_ID;
96 end if;
97
98 If (SQL%NOTFOUND) then
99 RAISE NO_DATA_FOUND;
100 End If;
101
102 END Delete_Row;
103
104 End CSC_CUSTOMIZED_PLANS_PKG;