[Home] [Help]
PACKAGE BODY: APPS.PO_ITEMS_SV3
Source
1 PACKAGE BODY PO_ITEMS_SV3 as
2 /* $Header: POXCOI3B.pls 115.5 2002/11/23 03:31:36 sbull ship $ */
3 /*============================= PO_ITEMS_SV2 ===============================*/
4
5 /*===========================================================================
6
7 PROCEDURE NAME: get_taxable_flag
8
9 ===========================================================================*/
10
11 PROCEDURE get_taxable_flag(x_item_id IN NUMBER,
12 x_item_org_id IN NUMBER,
13 x_ship_to_org_id IN NUMBER,
14 x_user_pref_taxable_flag IN VARCHAR2,
15 x_ship_to_org_taxable_flag IN OUT NOCOPY VARCHAR2,
16 x_item_org_taxable_flag IN OUT NOCOPY VARCHAR2,
17 x_po_default_taxable_flag IN VARCHAR2,
18 x_return_taxable_flag IN OUT NOCOPY VARCHAR2)
19 IS
20
21 x_progress VARCHAR2(3) := NULL;
22
23 BEGIN
24
25 -- dbms_output.put_line ('Begin get_taxable_flag ');
26
27 -- dbms_output.put_line ('Input user preference taxable_flag ' || x_user_pref_taxable_flag);
28 -- dbms_output.put_line ('Input item organization_taxable_flag ' || x_item_org_taxable_flag);
29 -- dbms_output.put_line ('Input ship_to organization_taxable_flag ' || x_ship_to_org_taxable_flag);
30 -- dbms_output.put_line ('Input PO default taxable_flag ' || x_po_default_taxable_flag);
31
32 -- Set the return taxable flag in the following priority
33 -- 1. User preference
34 -- 2. Ship_to organization
35 -- 3. Item organization
36 -- 4. PO default
37 --
38
39 IF x_user_pref_taxable_flag is NOT NULL THEN
40
41 -- Always use the user preference if exists
42
43 x_return_taxable_flag := x_user_pref_taxable_flag;
44
45 ELSE
46 -- Get item and ship_to organization taxable_flag from MTL_SYSTEM_ITEMS
47
48 IF x_item_id is not NULL THEN
49
50 IF x_item_org_id is not NULL AND
51 x_item_org_taxable_flag is NULL THEN
52
53 BEGIN
54
55 -- Get item organization taxable_flag
56
57 x_progress := '010';
58 SELECT msi.taxable_flag
59 INTO x_item_org_taxable_flag
60 FROM MTL_SYSTEM_ITEMS msi
61 WHERE msi.inventory_item_id = x_item_id
62 AND msi.organization_id = x_item_org_id;
63
64 -- dbms_output.put_line ('Get item organization_taxable_flag ' ||
65 -- bug 1555260 x_item_org_taxable_flag);
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 x_item_org_taxable_flag := NULL;
70
71 WHEN OTHERS THEN
72 -- dbms_output.put_line('In exception ');
73 po_message_s.sql_error('get_taxable_flag',
74 x_progress, sqlcode);
75 raise;
76 END;
77
78 END IF;
79
80 IF x_ship_to_org_id is not NULL AND
81 x_ship_to_org_taxable_flag is NULL THEN
82
83 BEGIN
84
85 -- Get ship_to organization taxable_flag
86
87 x_progress := '015';
88 SELECT msi.taxable_flag
89 INTO x_ship_to_org_taxable_flag
90 FROM MTL_SYSTEM_ITEMS msi
91 WHERE msi.inventory_item_id = x_item_id
92 AND msi.organization_id = x_ship_to_org_id;
93
94 -- dbms_output.put_line ('Get ship_to organization_taxable_flag ' ||
95 --bug 1555260 x_ship_to_org_taxable_flag);
96
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 x_ship_to_org_taxable_flag := NULL;
101
102 WHEN OTHERS THEN
103 -- dbms_output.put_line('In exception ');
104 po_message_s.sql_error('get_taxable_flag',
105 x_progress, sqlcode);
106 raise;
107
108 END;
109
110 END IF;
111
112 ELSE
113
114 -- No item id is passed.
115 -- Default item/ship_to organization taxable flag to NULL.
116
117 x_item_org_taxable_flag := NULL;
118 x_ship_to_org_taxable_flag := NULL;
119
120 END IF;
121
122 -- Decide the return_taxable_flag based on the following priority:
123 -- 1. Ship_to organization
124 -- 2. Item organization
125 -- 3. PO default
126
127 IF x_ship_to_org_taxable_flag is not NULL THEN
128 x_return_taxable_flag := x_ship_to_org_taxable_flag;
129 ELSIF x_item_org_taxable_flag is not NULL THEN
130 x_return_taxable_flag := x_item_org_taxable_flag;
131 ELSE
132 x_return_taxable_flag := x_po_default_taxable_flag;
133 END IF;
134
135 END IF;
136
137 -- dbms_output.put_line ('Return taxable_flag ' || x_return_taxable_flag);
138
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 x_return_taxable_flag := x_po_default_taxable_flag;
142
143 WHEN OTHERS THEN
144 -- dbms_output.put_line('In exception sqlcode ');
145 po_message_s.sql_error('get_taxable_flag',
146 x_progress, sqlcode);
147 raise;
148
149 END get_taxable_flag;
150
151
152
153 END PO_ITEMS_SV3;