1 PACKAGE BODY MyPackageName AS
2 /* $Header: PAXITMPB.pls 120.0.12010000.2 2009/01/23 06:38:28 nkapling noship $ */
3
4 ------------------------------------
5 -- FUNCTION/PROCEDURE IMPLEMENTATION
6 --
7 -- Replace all occurrences of 'MyProcName' in this file with the name of
8 -- your main procedure.
9 --
10 -- The template assumes that the calling place is 'ADJ' or 'REG' only
11 -- if other calling place is used then the logic should be modified.
12 --
13 -- *** WARNING! DO NOT CHANGE THE PARAMETERS TO MyProcName ***
14 --
15
16 PROCEDURE MyProcName( X_project_id IN NUMBER,
17 X_top_task_id IN NUMBER DEFAULT NULL,
18 X_calling_process IN VARCHAR2 DEFAULT NULL,
19 X_calling_place IN VARCHAR2 DEFAULT NULL,
20 X_amount IN NUMBER DEFAULT NULL,
21 X_percentage IN NUMBER DEFAULT NULL,
22 X_rev_or_bill_date IN DATE DEFAULT NULL,
23 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
24 X_bill_extension_id IN NUMBER DEFAULT NULL,
25 X_request_id IN NUMBER DEFAULT NULL) IS
26
27 -- Declare any cursors that your procedure might need here.
28
29 CURSOR AdjLogic IS
30 SELECT event_num, event_type, organization_id
31 FROM pa_billing_orig_events_v oe
32 -- Add a WHERE clause if needed. Example:
33 -- ,pa_billing_assignments ba
34 -- WHERE ba.billing_assignment_id = oe.billing_assignment_id
35 -- AND ba.billing_extension_id = X_extn_id
36 ;
37
38 -- Here you should define all the variable needed by your main procedure
39 -- for example :
40 -- revenue_amount REAL;
41 -- or new_org_id NUMBER(6);
42 -- or project_name VARCHAR2(30);
43 L_amount_to_bill REAL := 0;
44 L_amount_to_accrue REAL := 0;
45
46 amount NUMBER(22,5);
47 revenue NUMBER(10,2) := 0;
48 invoice NUMBER(10,2) := 0;
49 cost NUMBER(10,2) := 0;
50 L_event_num_reversed NUMBER;
51 L_event_type VARCHAR2(30);
52 L_organization_id NUMBER;
53 l_error_message VARCHAR2(240);
54 l_status NUMBER;
55 l_cost_budget_type_code VARCHAR2(30);
56 l_rev_budget_type_code VARCHAR2(30);
57
58 BEGIN
59
60 -- Perform processing here.
61 -- You may remove parameters that are optional and that you do not need to
62 -- specify from the pre-defined public procedures listed below:
63 -- Look at documentation in $PA_TOP/install/sql/PAXIPUBS.pls for parameter
64 -- descriptions and details.
65 -- You may select amounts being processed in the current run from the two
66 -- views as below. The rows returned by these views are restricted to
67 -- the rows being processed by the current run for the project/task being
68 -- currently processed.
69
70 IF (X_calling_process = 'Revenue') THEN
71 SELECT sum(nvl(revenue_amount,0))
72 INTO revenue
73 FROM pa_billing_rev_transactions_v;
74 ELSE
75 SELECT sum(nvl(bill_amount,0))
76 INTO invoice
77 FROM pa_billing_inv_transactions_v;
78 END IF;
79
80
81 pa_billing_pub.get_budget_amount(
82 X2_project_id => X_project_id,
83 X2_task_id => X_top_task_id,
84 X2_revenue_amount => revenue,
85 X2_cost_amount => cost,
86 X_cost_budget_type_code => l_cost_budget_type_code,
87 X_rev_budget_type_code => l_rev_budget_type_code,
88 X_status => l_status,
89 X_error_message => l_error_message);
90
91 -- You need to put proper logic to get the original event num from
92 -- the pa_billing_orig_events_v view.
93
94 IF (X_calling_place = 'ADJ' AND X_calling_process = 'Invoice') THEN
95
96 FOR AdjEv IN AdjLogic LOOP
97
98 -- Do Your Own ADJustment logic.
99 L_event_num_reversed := AdjEv.event_num;
100 L_event_type := AdjEv.event_type;
101 L_organization_id := AdjEv.organization_id;
102 -- Need your own logic to get proper event info to insert a new event.
103 -- For example,
104 L_amount_to_accrue := revenue;
105 L_amount_to_bill := invoice;
106
107 -- Need to pass proper parameters for your own billing extension
108 -- when you call the pa_billing_PUB.Insert_Event procedure.
109 -- You must pass in either a revenue amount or a bill amount to
110 -- insert_event. See Manual for details.
111 pa_billing_pub.Insert_Event (X_rev_amt => L_amount_to_accrue,
112 X_bill_amt => L_amount_to_bill,
113 X_project_id => X_project_id,
114 X_event_type => L_event_type,
115 X_top_task_id => X_top_task_id,
116 X_organization_id => L_organization_id,
117 X_completion_date => SYSDATE,
118 X_event_num_reversed => L_event_num_reversed,
119 X_status => l_status,
120 X_error_message => l_error_message);
121
122 END LOOP;
123
124 ELSE
125
126 -- Do your own logic here.
127 L_event_num_reversed := NULL;
128 -- Need your own logic to get proper event info to insert a new event.
129 -- For example,
130 L_amount_to_accrue := revenue;
131 L_amount_to_bill := invoice;
132
133 -- Need to pass proper parameters for your own billing extension
134 -- when you call the pa_billing_PUB.Insert_Event procedure.
135 -- You must pass in either a revenue amount or a bill amount to
136 -- insert_event. See Manual for details.
137 pa_billing_PUB.Insert_Event (
138 X_rev_amt => L_amount_to_accrue,
139 X_bill_amt => L_amount_to_bill,
140 X_event_description => '',
141 X_event_num_reversed => L_event_num_reversed,
142 X_status => l_status,
143 X_error_message => l_error_message);
144
145 END IF;
146
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 pa_billing_pub.Insert_Message(X_inserting_procedure_name =>'MyProcName',
151 X_message => 'Error Message',
152 X_status => l_status,
153 X_error_message => l_error_message);
154 END MyProcName;
155
156 END MyPackageName;
[Home] [Help]