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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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)
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
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';
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:
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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)
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
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';
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
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:
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:
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,
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 ||' (+)');
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:
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:
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;