1 PACKAGE BODY PO_VAL_PRICE_ADJS AS
2 -- $Header: PO_VAL_PRICE_ADJS.plb 120.0.12010000.1 2009/06/01 23:21:25 ababujan noship $
3
4 c_ENTITY_TYPE_PRICE_ADJ CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_PRICE_ADJ;
5
6 -- constants for columns:
7 c_UPDATED_FLAG CONSTANT VARCHAR2(30) := 'UPDATED_FLAG';
8 c_CHANGE_REASON_CODE CONSTANT VARCHAR2(30) := 'CHANGE_REASON_CODE';
9 c_CHANGE_REASON_TEXT CONSTANT VARCHAR2(30) := 'CHANGE_REASON_TEXT';
10
11 -- The module base for this package.
12 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
13 PO_LOG.get_package_base('PO_VAL_PRICE_ADJS');
14
15 -- The module base for the subprogram.
16 D_ovr_chng_reas_code_not_null CONSTANT VARCHAR2(100) :=
17 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ovr_chng_reas_code_not_null');
18
19 -- The module base for the subprogram.
20 D_ovr_chng_reas_text_not_null CONSTANT VARCHAR2(100) :=
21 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ovr_chng_reas_text_not_null');
22
23 ----------------------------------------------------------------------------------------------
24 -- Ensures that the change reason code is not null if the updated flag is Y
25 ----------------------------------------------------------------------------------------------
26 PROCEDURE ovr_chng_reas_code_not_null(
27 p_price_adj_id_tbl IN PO_TBL_NUMBER
28 , p_updated_flag_tbl IN PO_TBL_VARCHAR1
29 , p_change_reason_code_tbl IN PO_TBL_VARCHAR30
30 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
31 , x_result_type OUT NOCOPY VARCHAR2
32 )
33 IS
34 d_mod CONSTANT VARCHAR2(100) := D_ovr_chng_reas_code_not_null;
35 l_results_count NUMBER;
36 BEGIN
37
38 IF (x_results IS NULL) THEN
39 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
40 END IF;
41
42 IF PO_LOG.d_proc THEN
43 PO_LOG.proc_begin(d_mod,'p_price_adj_id_tbl',p_price_adj_id_tbl);
44 PO_LOG.proc_begin(d_mod,'p_updated_flag_tbl',p_updated_flag_tbl);
45 PO_LOG.proc_begin(d_mod,'p_change_reason_code_tbl',p_change_reason_code_tbl);
46 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
47 END IF;
48
49 l_results_count := x_results.result_type.COUNT;
50
51 FOR i IN 1 .. p_price_adj_id_tbl.COUNT LOOP
52 IF (NVL(p_updated_flag_tbl(i),'N') = 'Y' AND p_change_reason_code_tbl(i) IS NULL)
53 THEN
54 x_results.add_result(
55 p_entity_type => c_ENTITY_TYPE_PRICE_ADJ
56 , p_entity_id => p_price_adj_id_tbl(i)
57 , p_column_name => c_CHANGE_REASON_CODE
58 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
59 );
60 END IF;
61 END LOOP;
62
63 IF (l_results_count < x_results.result_type.COUNT) THEN
64 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
65 ELSE
66 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
67 END IF;
68
69 IF PO_LOG.d_proc THEN
70 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
71 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 IF PO_LOG.d_exc THEN
77 PO_LOG.exc(d_mod,0,NULL);
78 END IF;
79 RAISE;
80 END ovr_chng_reas_code_not_null;
81
82 ----------------------------------------------------------------------------------------------
83 -- Ensures that the change reason text is not null if the updated flag is Y
84 ----------------------------------------------------------------------------------------------
85 PROCEDURE ovr_chng_reas_text_not_null(
86 p_price_adj_id_tbl IN PO_TBL_NUMBER
87 , p_updated_flag_tbl IN PO_TBL_VARCHAR1
88 , p_change_reason_text_tbl IN PO_TBL_VARCHAR2000
89 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
90 , x_result_type OUT NOCOPY VARCHAR2
91 )
92 IS
93 d_mod CONSTANT VARCHAR2(100) := D_ovr_chng_reas_text_not_null;
94 l_results_count NUMBER;
95 BEGIN
96
97 IF (x_results IS NULL) THEN
98 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
99 END IF;
100
101 IF PO_LOG.d_proc THEN
102 PO_LOG.proc_begin(d_mod,'p_price_adj_id_tbl',p_price_adj_id_tbl);
103 PO_LOG.proc_begin(d_mod,'p_updated_flag_tbl',p_updated_flag_tbl);
104 PO_LOG.proc_begin(d_mod,'p_change_reason_text_tbl',p_change_reason_text_tbl);
105 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
106 END IF;
107
108 l_results_count := x_results.result_type.COUNT;
109
110 FOR i IN 1 .. p_price_adj_id_tbl.COUNT LOOP
111 IF (NVL(p_updated_flag_tbl(i),'N') = 'Y' AND p_change_reason_text_tbl(i) IS NULL)
112 THEN
113 x_results.add_result(
114 p_entity_type => c_ENTITY_TYPE_PRICE_ADJ
115 , p_entity_id => p_price_adj_id_tbl(i)
116 , p_column_name => c_CHANGE_REASON_TEXT
117 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
118 );
119 END IF;
120 END LOOP;
121
122 IF (l_results_count < x_results.result_type.COUNT) THEN
123 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
124 ELSE
125 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
126 END IF;
127
128 IF PO_LOG.d_proc THEN
129 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
130 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
131 END IF;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF PO_LOG.d_exc THEN
136 PO_LOG.exc(d_mod,0,NULL);
137 END IF;
138 RAISE;
139 END ovr_chng_reas_text_not_null;
140
141 END PO_VAL_PRICE_ADJS;