1 PACKAGE ozf_accrual_engine AS
2 /* $Header: ozfacres.pls 120.4 2005/12/30 15:19:46 feliu ship $ */
3
4
5 -- constant used for gl_posted_flag in utilization table: do not modify the values as checkbook/claim use Y/N/F/NULL directly
6 -- 'Y': posted to gl successfully
7 -- 'N': waiting to post to gl
8 -- 'F': failed to post to gl
9 -- 'O': do not post to gl, used for customer accrual budget with liability off
10 -- null: do not post to gl,
11 -- 1) for 'REQUEST/TRANSFER',
12 -- 2) for 'UTILIZED' offer, means 'Create GL for off invoice' is off, so utilized/earned/paid updated the same time
13 -- 3) for 'UTILIZED' marketing object, means utilized/earned/paid the same time
14 G_GL_FLAG_YES CONSTANT VARCHAR2(1) := 'Y';
15 G_GL_FLAG_NO CONSTANT VARCHAR2(1) := 'N';
16 G_GL_FLAG_FAIL CONSTANT VARCHAR2(1) := 'F';
17 G_GL_FLAG_NOLIAB CONSTANT VARCHAR2(1) := 'X';
18 G_GL_FLAG_NULL CONSTANT VARCHAR2(1) := NULL;
19
20 --
21 -- Procedure Name
22 -- Get_Message
23 -- Purpose
24 -- This procedure collects order updates from the Order Capture Notification
25 -- API. Started from a concurrent process, it is an infinite loop which
26 -- gets the latest notification off of the queue.
27 --
28 PROCEDURE get_message(
29 x_errbuf OUT NOCOPY VARCHAR2
30 ,x_retcode OUT NOCOPY NUMBER
31 ,p_run_exception IN VARCHAR2 := 'N'
32 ,p_debug IN VARCHAR2 := 'N'
33 );
34
35 --
36 -- Procedure Name
37 -- Adjust_Accrual
38 -- Purpose
39 -- This procedure will accept the Line_Adj_tbl_Type and old_Line_Adj_Tbl_Type
40 -- and calculate the accrual difference for a offer, going backward it will find
41 -- out the associated fund and update the accrued amount.
42 PROCEDURE adjust_accrual(
43 p_api_version IN NUMBER
44 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
45 ,p_commit IN VARCHAR2 := fnd_api.g_false
46 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
47 ,x_return_status OUT NOCOPY VARCHAR2
48 ,x_msg_count OUT NOCOPY NUMBER
49 ,x_msg_data OUT NOCOPY VARCHAR2
50 ,p_line_adj_tbl IN oe_order_pub.line_adj_tbl_type
51 ,p_old_line_adj_tbl IN oe_order_pub.line_adj_tbl_type
52 ,p_header_rec IN oe_order_pub.header_rec_type := NULL
53 ,p_exception_queue IN VARCHAR2 := fnd_api.g_false );
54 --//added by mpande
55 -- Procedure Name
56 -- calculate_accrual_amount
57 -- Purpose
58 -- This procedure will accept p_src_id
59 -- and return a PL/SQL table consisting of all the funds and its
60 -- the contributed amount rolling up to the top most level
61 TYPE ozf_fund_amt_rec_type IS RECORD(
62 ofr_src_type VARCHAR2(40), -- the sys_arc_qualifier for the offer for eg 'FUND','CAMP'
63 ofr_src_id NUMBER, -- the id for the sys_arc_qualifier for eg FUND_ID ,CAMP_ID
64 earned_amount NUMBER, -- the contribution amount of the fund or camp to this offer
65 budget_currency VARCHAR2(20) -- the sys_arc_qualifier for the offer for eg 'FUND','CAMP'
66 );
67 TYPE ozf_fund_amt_tbl_type IS TABLE OF ozf_fund_amt_rec_type
68 INDEX BY BINARY_INTEGER;
69
70 TYPE ozf_adjusted_amt_rec_type IS RECORD(
71 order_header_id NUMBER,
72 order_line_id NUMBER,
73 price_adjustment_id NUMBER,
74 qp_list_header_id NUMBER,
75 product_id NUMBER,
76 earned_amount NUMBER,
77 offer_currency VARCHAR2(30)
78 );
79
80 TYPE ozf_adjusted_amt_tbl_type IS TABLE OF ozf_adjusted_amt_rec_type
81 INDEX BY BINARY_INTEGER;
82
83 ------------------------------------------------------------------------------
84 -- Procedure Name
85 -- calculate_accrual_amount
86 -- Purpose
87 -- This procedure performs accruals for all offers for the folow
88 -- 1) Order Managemnt Accruals
89 -- 2) Backdating Adjustment
90 -- 3) Volume Offer Backdating
91 -- History
92 -- 10/18/2002 mpande Created
93 ------------------------------------------------------------------------------
94
95 PROCEDURE calculate_accrual_amount(
96 x_return_status OUT NOCOPY VARCHAR2
97 ,p_src_id IN NUMBER
98 ,p_earned_amt IN NUMBER
99 ,p_cust_account_type IN VARCHAR2 := NULL
100 ,p_cust_account_id IN NUMBER := NULL
101 ,p_product_item_id IN NUMBER := NULL
102 ,x_fund_amt_tbl OUT NOCOPY ozf_fund_amt_tbl_type);
103
104 ------------------------------------------------------------------------------
105 -- Procedure Name
106 -- Accrue_offers
107 -- Purpose
108 -- This procedure performs accruals for all offers for the folow
109 -- 1) Order Managemnt Accruals
110 -- 2) Backdating Adjustment
111 -- 3) Volume Offer Backdating
112 -- 4) reprocess all failed gl postings
113 -- History
114 -- 10/18/2002 mpande Created
115 ------------------------------------------------------------------------------
116
117 PROCEDURE Accrue_offers (x_errbuf OUT NOCOPY VARCHAR2,
118 x_retcode OUT NOCOPY NUMBER,
119 p_run_exception IN VARCHAR2 := 'N',
120 p_run_backdated_adjustment IN VARCHAR2 := 'N',
121 p_run_volume_off_adjustment IN VARCHAR2 := 'N',
122 p_run_unposted_gl IN VARCHAR2 := 'N',
123 p_debug IN VARCHAR2 := 'N');
124
125
126 /*----------------------------------------------------------------------------
127 -- Procedure Name
128 -- post_accrual_to_budget
129 -- Purpose
130 -- This procedure will post accrual to budget proportionally, and create utilization records
131 -- extracted from adjust_accrual so it can be reused
132 --
133 -- Parameters:
134 --
135 -- History
136 -- created yzhao 03/21/03
137 ------------------------------------------------------------------------------*/
138 PROCEDURE post_accrual_to_budget (
139 p_adj_amt_tbl IN ozf_adjusted_amt_tbl_type,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2);
143
144
145 ------------------------------------------------------------------------------
146 -- Procedure Name
147 -- post_accrual_to_gl
148 -- Purpose
149 -- This procedure posts one accrual record to GL
150 -- History
151 -- 03/19/2003 Ying Zhao Created
152 ------------------------------------------------------------------------------
153 PROCEDURE post_accrual_to_gl(
154 p_util_utilization_id IN NUMBER,
155 p_util_object_version_number IN NUMBER,
156 p_util_amount IN NUMBER,
157 p_util_plan_type IN VARCHAR2,
158 p_util_plan_id IN NUMBER,
159 p_util_plan_amount IN NUMBER,
160 p_util_utilization_type IN VARCHAR2,
161 p_util_fund_id IN NUMBER,
162 p_util_acctd_amount IN NUMBER,
163 p_adjust_paid_flag IN BOOLEAN := FALSE,
164 p_util_org_id IN NUMBER := NULL,
165 x_gl_posted_flag OUT NOCOPY VARCHAR2,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2
169 );
170
171
172 PROCEDURE post_related_accrual_to_gl(
173 p_utilization_id IN NUMBER,
174 p_utilization_type IN VARCHAR2,
175 p_gl_date IN DATE := NULL,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2);
179
180 /*kdass - funds accrual process by business event descoped due to performance issues
181 PROCEDURE process_order_queue (x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2
184 );
185
186
187 PROCEDURE increase_order_message_counter;
188
189 FUNCTION event_subscription
190 (p_subscription_guid in raw,
191 p_event in out NOCOPY wf_event_t) return varchar2;
192 */
193
194 END ozf_accrual_engine;
[Home] [Help]