[Home] [Help]
PACKAGE BODY: APPS.PN_REC_EXPCL_DTL_PKG
Source
1 PACKAGE BODY pn_rec_expcl_dtl_pkg AS
2 /* $Header: PNRECLSB.pls 120.2 2005/11/30 23:39:30 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
10 -- 28-NOV-05 pikhar o fetched org_id using cursor
11 -------------------------------------------------------------------------------
12 PROCEDURE insert_row(
13 x_org_id pn_rec_expcl_dtl.org_id%TYPE,
14 x_expense_class_id pn_rec_expcl_dtl.expense_class_id%TYPE,
15 x_expense_line_id pn_rec_expcl_dtl.expense_line_id%TYPE,
16 x_expense_class_dtl_id IN OUT NOCOPY pn_rec_expcl_dtl.expense_class_dtl_id%TYPE,
17 x_status pn_rec_expcl_dtl.status%TYPE,
18 x_def_area_cls_id pn_rec_expcl_dtl.default_area_class_id%TYPE,
19 x_cls_line_fee_bf_ct pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE,
20 x_cls_line_fee_af_ct pn_rec_expcl_dtl.cls_line_fee_after_contr%TYPE,
21 x_cls_line_portion_pct pn_rec_expcl_dtl.cls_line_portion_pct%TYPE,
22 x_last_update_date pn_rec_expcl_dtl.last_update_date%TYPE,
23 x_last_updated_by pn_rec_expcl_dtl.last_updated_by%TYPE,
24 x_creation_date pn_rec_expcl_dtl.creation_date%TYPE,
25 x_created_by pn_rec_expcl_dtl.created_by%TYPE,
26 x_last_update_login pn_rec_expcl_dtl.last_update_login%TYPE)
27 IS
28 l_desc VARCHAR2(100) := 'pn_rec_expcl_dtl_pkg.insert_row';
29
30 CURSOR org_cur IS
31 SELECT org_id
32 FROM pn_rec_exp_line_all
33 WHERE expense_line_id = x_expense_line_id ;
34
35 l_org_id NUMBER;
36
37 BEGIN
38
39 pnp_debug_pkg.debug(l_desc ||' (+)');
40
41
42 IF x_org_id IS NULL THEN
43 FOR rec IN org_cur LOOP
44 l_org_id := rec.org_id;
45 END LOOP;
46 ELSE
47 l_org_id := x_org_id;
48 END IF;
49
50 INSERT INTO pn_rec_expcl_dtl_all(
51 org_id,
52 expense_class_id,
53 expense_line_id,
54 expense_class_dtl_id,
55 status,
56 default_area_class_id,
57 cls_line_fee_before_contr,
58 cls_line_fee_after_contr,
59 cls_line_portion_pct,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login )
65 VALUES (
66 l_org_id,
67 x_expense_class_id,
68 x_expense_line_id,
69 pn_rec_expcl_dtl_s.nextval,
70 x_status,
71 x_def_area_cls_id,
72 x_cls_line_fee_bf_ct,
73 x_cls_line_fee_af_ct,
74 x_cls_line_portion_pct,
75 x_last_update_date,
76 x_last_updated_by,
77 x_creation_date,
78 x_created_by,
79 x_last_update_login
80 ) RETURNING expense_class_dtl_id INTO x_expense_class_dtl_id;
81
82 pnp_debug_pkg.debug(l_desc ||' (-)');
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
87 app_exception.raise_exception;
88 END insert_row;
89
90 -------------------------------------------------------------------------------
91 -- PROCDURE : UPDATE_ROW
92 -- INVOKED FROM : update_row procedure
93 -- PURPOSE : updates the row
94 -- HISTORY :
95 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
96 -------------------------------------------------------------------------------
97 PROCEDURE update_row(
98 x_expense_class_id pn_rec_expcl_dtl.expense_class_id%TYPE,
99 x_expense_line_id pn_rec_expcl_dtl.expense_line_id%TYPE,
100 x_expense_class_dtl_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE,
101 x_status pn_rec_expcl_dtl.status%TYPE,
102 x_def_area_cls_id pn_rec_expcl_dtl.default_area_class_id%TYPE,
103 x_cls_line_fee_bf_ct pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE,
104 x_cls_line_fee_af_ct pn_rec_expcl_dtl.cls_line_fee_after_contr%TYPE,
105 x_cls_line_portion_pct pn_rec_expcl_dtl.cls_line_portion_pct%TYPE,
106 x_last_update_date pn_rec_expcl_dtl.last_update_date%TYPE,
107 x_last_updated_by pn_rec_expcl_dtl.last_updated_by%TYPE,
108 x_creation_date pn_rec_expcl_dtl.creation_date%TYPE,
109 x_created_by pn_rec_expcl_dtl.created_by%TYPE,
110 x_last_update_login pn_rec_expcl_dtl.last_update_login%TYPE)
111 IS
112 l_desc VARCHAR2(100) := 'pn_rec_expcl_dtl_pkg.update_row';
113 BEGIN
114
115 pnp_debug_pkg.debug(l_desc ||' (+)');
116
117 UPDATE pn_rec_expcl_dtl_all
118 SET
119 expense_class_id = x_expense_class_id,
120 expense_line_id = x_expense_line_id,
121 status = x_status,
122 default_area_class_id = x_def_area_cls_id,
123 cls_line_fee_before_contr = x_cls_line_fee_bf_ct,
124 cls_line_fee_after_contr = x_cls_line_fee_af_ct,
125 cls_line_portion_pct = x_cls_line_portion_pct,
126 last_update_date = x_last_update_date,
127 last_updated_by = x_last_updated_by,
128 creation_date = x_creation_date,
129 created_by = x_created_by,
130 last_update_login = x_last_update_login
131 WHERE expense_class_dtl_id = x_expense_class_dtl_id;
132
133 pnp_debug_pkg.debug(l_desc ||' (-)');
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
138 app_exception.raise_exception;
139 END update_row;
140
141 -------------------------------------------------------------------------------
142 -- PROCDURE : DELETE_ROW
143 -- INVOKED FROM : delete_row procedure
144 -- PURPOSE : deletes the row
145 -- HISTORY :
146 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
147 -------------------------------------------------------------------------------
148 PROCEDURE delete_row(x_expense_class_dtl_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE) IS
149 l_desc VARCHAR2(100) := 'pn_rec_expcl_dtl_pkg.delete_row';
150 BEGIN
151
152 pnp_debug_pkg.debug(l_desc ||' (+)');
153
154 DELETE pn_rec_expcl_dtl_all
155 WHERE expense_class_dtl_id = x_expense_class_dtl_id;
156
157 pnp_debug_pkg.debug(l_desc ||' (-)');
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
162 app_exception.raise_exception;
163 END delete_row;
164
165 END pn_rec_expcl_dtl_pkg;