1 PACKAGE BODY pa_tax_client_extn_drv AS
2 /* $Header: PAXVDTXB.pls 120.7 2007/10/26 12:33:48 arbandyo ship $ */
3 -- Private procedure for setting invoice distribution warning
4
5 /*----------------------------------------------------------------------------+
6 | This Private Procedure Insert_Distrbution_Warning Inserts draft Invoice |
7 | distribution warning. |
8 +----------------------------------------------------------------------------*/
9 Procedure Insert_Distribution_Warning( P_Project_ID in number,
10 P_User_ID in number,
11 P_Request_ID in number,
12 P_Error_Message_Code in varchar2) is
13
14 l_error_message pa_lookups.meaning%TYPE;
15
16 BEGIN
17
18 -- If Project id or draft Invoice Number is changed then insert the
19 -- distribution warning.
20
21 If (pa_tax_client_extn_drv.G_Project_Id <> P_Project_ID or
22 pa_tax_client_extn_drv.G_Prv_Draft_Invoice_num
23 <> pa_tax_client_extn_drv.G_Draft_Invoice_num )
24 Then
25 If pa_tax_client_extn_drv.G_Draft_Invoice_num is not NULL
26 Then
27 BEGIN
28 SELECT Meaning
29 INTO l_error_message
30 FROM PA_Lookups
31 WHERE Lookup_Type = 'OUTPUT TAX'
32 AND Lookup_Code = P_Error_Message_Code;
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 l_error_message := P_Error_Message_Code;
36 END;
37
38 INSERT INTO PA_DISTRIBUTION_WARNINGS
39 (
40 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
41 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
42 )
43 VALUES
44 (
45 P_Project_ID, pa_tax_client_extn_drv.G_Draft_Invoice_Num,sysdate,
46 P_User_ID,sysdate, P_User_ID, P_Request_ID, l_error_message
47 );
48 Else
49
50 -- Set the Error Code
51 pa_tax_client_extn_drv.G_error_Code := P_Error_Message_Code;
52
53 End if;
54
55 -- Set the Global to avoid unnecessary loop
56 pa_tax_client_extn_drv.G_Project_Id := P_Project_ID;
57 pa_tax_client_extn_drv.G_Prv_Draft_Invoice_num := G_Draft_Invoice_num;
58
59 End If;
60
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 RAISE;
65 END Insert_Distribution_Warning;
66
67 -- Public part of the package starts here
68
69 PROCEDURE get_tax_code
70 ( P_project_id IN NUMBER,
71 P_customer_id IN NUMBER DEFAULT NULL,
72 P_bill_to_site_use_id IN NUMBER DEFAULT NULL,
73 P_ship_to_site_use_id IN NUMBER DEFAULT NULL,
74 P_set_of_books_id IN NUMBER DEFAULT NULL,
75 P_expenditure_item_id IN NUMBER DEFAULT NULL,
76 P_event_id IN NUMBER DEFAULT NULL,
77 P_line_type IN VARCHAR2 DEFAULT NULL,
78 P_request_id IN NUMBER DEFAULT NULL,
79 P_user_id IN NUMBER DEFAULT NULL,
80 X_output_Tax_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
81 IS
82
83 --l_vat_tax_id Number; --commented by hsiu
84 l_tax_code Varchar2(30);
85 l_dummy Varchar2(1);
86
87 BEGIN
88
89 -- Call the Client extension Driver
90
91 pa_client_extn_output_tax.get_tax_code
92 ( P_project_id => P_project_id,
93 P_customer_id => P_customer_id,
94 P_bill_to_site_use_id => P_bill_to_site_use_id,
95 P_ship_to_site_use_id => P_ship_to_site_use_id,
96 P_set_of_books_id => P_set_of_books_id,
97 P_expenditure_item_id => P_expenditure_item_id,
98 P_event_id => P_event_id,
99 P_line_type => P_line_type,
100 P_request_id => P_request_id,
101 P_user_id => P_user_id,
102 X_output_Tax_code => l_tax_code);
103 -- X_vat_tax_id => l_vat_tax_id ); --commented by hsiu
104
105 -- If l_vat_tax_id IS Not NULL --commented by hsiu
106 If l_tax_code IS NOT NULL
107 Then
108 Begin
109 -- Verify the existance of the tax Id
110
111 Select 'x'
112 into l_dummy
113 from pa_output_tax_code_txn_v
114 where tax_code = l_tax_code
115 and org_id in (PA_MOAC_UTILS.GET_CURRENT_ORG_ID, -99) /*Added condition for Bug 5255736*/
116 and G_Invoice_Date between start_date_active and nvl(end_date_active,sysdate); /* Added for Bug 6521026 */
117 -- where vat_tax_id = l_vat_tax_id;--commented by hsiu
118
119 -- Set the output Tax Id
120 x_output_tax_code := l_tax_code;
121 -- X_vat_tax_id := l_vat_tax_id; --commented by hsiu
122
123 Exception
124
125 When NO_DATA_FOUND
126 Then
127 -- If Client Extension returns Invalid Tax Id,
128 -- Set distribution warning and output vat tax id as Null.
129
130 Insert_Distribution_Warning
131 (P_Project_ID => P_Project_ID,
132 P_User_ID => P_User_ID,
133 P_Request_ID => P_Request_ID,
134 P_Error_Message_Code => 'INVALID_TAX_ID');
135
136 -- X_vat_tax_id := NULL;
137 x_output_tax_code := NULL;
138 End;
139 End If;
140
141 EXCEPTION
142
143 When Others
144 Then
145 /* ATG Changes */
146
147 X_output_Tax_code := null;
148
149 Raise;
150
151 END get_tax_code;
152
153 END pa_tax_client_extn_drv;