1 PACKAGE okl_subsidy_pool_trx_pvt AUTHID CURRENT_USER AS
2 /* $Header: OKLRSIXS.pls 120.1 2005/07/08 23:55:08 cklee noship $ */
3
4 subtype sixv_rec_type is OKL_SIX_PVT.sixv_rec_type;
5 subtype sixv_tbl_type is OKL_SIX_PVT.sixv_tbl_type;
6 ---------------------------------------------------------------------------
7 -- GLOBAL EXCEPTIONS
8 ---------------------------------------------------------------------------
9 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
10
11 ---------------------------------------------------------------------------
12 -- GLOBAL VARIABLES
13 ---------------------------------------------------------------------------
14 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SUBSIDY_POOL_TRX_PVT';
15 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
16 G_API_TYPE CONSTANT VARCHAR2(30) := '_PVT';
17 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
18 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
19
20 ------------------------------------------------------------------------------
21 -- PROCEDURE create_pool_transaction
22 ------------------------------------------------------------------------------
23 -- Start of comments
24 --
25 -- Procedure Name : create_pool_transaction
26 -- Description : This procedure is a wrapper that creates transaction
27 -- records for subsidy pool. Note that this procedure will
28 -- not report any error if the subsidy is not attached with
29 -- a pool
30 --
31 -- Business Rules : this procedure is used to add to pool balance or reduce
32 -- from pool balance. the trx_type_code determines this
33 -- action. This procedure inserts records into the
34 -- OKL_TRX_SUBSIDY_POOLS table irrespective of trx_type_code
35 -- . records can never be updated or deleted from this table
36 -- .
37 --
38 -- The following rules will be validated before create a
39 -- transaction:
40 --
41 -- 1. The subsidy pool is active.
42 --
43 -- 2. The subisdy pool is not expired -- the effectve to
44 -- date of the pool must equal or greater than the
45 -- current date.
46 -- System will by pass the expiration check if
47 -- TRX_TYPE_CODE = 'ADDITION', since this action will
48 -- not reduce the pool balance, but it can be used for
49 -- reporting purpose.
50 --
51 -- 3. The pool balance must not less than zero after the
52 -- reduction of the transaction.
53 --
54 -- 4. The asset subsidy transaction date must within the
55 -- subsidy dates as well as subisdy pool dates.
56 --
57 -- 5. Validate if the ADDITION operation on the subsidy pool
58 -- to reduce the balance that less than zero.
59 -- Logical speaking, caller should pass the original
60 -- REDUCTION subsidy pool amount, this check will avoid
61 -- un-normal operation of the pool transaction.
62 --
63 -- 6. Valiadte if conversion rate is available when system
64 -- convert from the ransaction currency to the pool
65 -- curreny.
66 --
67 -- 7. System will use OKL standard API:
68 -- okl_accounting_util.cross_currency_round_amount
69 -- to round subisdy amount.
70 --
71 -- Parameters : required parameters are below:
72 -- 1) TRX_TYPE_CODE: Subsidy Pool tansaction type
73 -- 'ADDITION' - add the subsidy amount to the pool
74 -- 'REDUCTION' - reduce the subsidy amount from the pool
75 -- Note: refer from fnd lookups type:
76 -- OKL_SUB_POOL_LINE_TYPE
77 --
78 -- 2) SOURCE_TYPE_CODE: Source entity type
79 -- 'LEASE_CONTRACT' - Contract
80 -- 'SALES_QUOTE' - Quote
81 -- 'LEASE_APPLICATION' - Lease Application
82 -- Note: refer from fnd lookups type:
83 -- OKL_SUB_POOL_TRX_SOURCE_TYPE
84 --
85 -- 3) SOURCE_OBJECT_ID: Source entity PK
86 --
87 -- 4) DNZ_ASSET_NUMBER: OKL asset number/Fixed asset number
88 -- Note: This asset number may not exists in fixed asset
89 -- for Sales Quote, Lease App, or contract before book.
90 --
91 -- 5) VENDOR_ID: Vendor PK
92 -- Note: refer from PO_VENDORS.vendor_id
93 --
94 -- 6) SOURCE_TRX_DATE: Source instance transaction date
95 -- For Sales Quote and Lease Application: Asset expected
96 -- start date
97 -- For Lease contract:
98 -- Activates a contract -- Asset start date
99 -- Rebooks a contarct -- Rebook transaction date
100 -- Splits a contract -- Split date
101 -- Reverse a contarct -- Reversal date
102 -- Note: Validate if the source transaction date is
103 -- between the effective dates of subsidy and the
104 -- subsidy pool in that order.
105 --
106 -- 7) SUBSIDY_ID: Subsidy entity PK
107 -- Note: 1. refer from OKL_SUBSIDIES_B.ID.
108 -- 2. Subsidy may or may not associate with a
109 -- subsidy pool. Subisdy can associate with a
110 -- pool at a time.
111 --
112 -- 8) TRX_REASON_CODE: Subsidy pool transaction reason.
113 -- 'ACCEPTE_QUOTE' - Accepted Sales Quote
114 -- 'ACCEPT_LEASE_APP' - Accepted Lease Application
115 -- 'ACTIVATE_CONTRACT' - Activate Lease Contract
116 -- 'APPROVE_LEASE_APP' - Approve Lease Application
117 -- 'APPROVE_QUOTE' - Approve Sales Quote
118 -- 'CANCEL_LEASE_OPP' - Cancel Lease Opportunity
119 -- 'CANCEL_QUOTE' - Cancel Sales Quote
120 -- 'REBOOK_CONTRACT' - Rebook Lease Contract
121 -- 'REVERSE_CONTRACT' - Reverse Lease Contract
122 -- 'SPLIT_CONTRACT' - Split Lease Contract
123 -- 'UPDATE_APPROVED_QUOTE'- Update Approved Sales Quote
124 -- 'UPDATE_LEASE_APP' - Update Lease Application
125 -- 'WITHDRAW_LEASE_APP' - Withdraw Lease Application
126 -- Note: refer from fnd lookups type:
127 -- OKL_SUB_POOL_TRX_REASON_TYPE
128 --
129 -- 9) TRX_CURRENCY_CODE: Asset subisdy currency
130 -- Note: refer from gl_currencies
131 --
132 -- 10) TRX_AMOUNT: Asset subisdy amount
133 --
134 -- 11) SUBSIDY_POOL_AMOUNT: Converted subsidy amount based
135 -- on the corresponding subisdy pool currency and
136 -- conversion type.
137 -- If TRX_TYPE_CODE = 'ADDITION', then this column is
138 -- required.
139 -- For example, there is a subisdy pool amount USD $501
140 -- when Quote XYZ approved. User cancel the lease
141 -- opportunity, Hence, we need to get the original
142 -- converted subsidy pool amount when add back to the
143 -- pool. This will avoid the converson or rounding issue
144 -- by the different time frames when revese the original
145 -- amount to the pool.
146 --
147 -- 12) CONVERSION_RATE: Conversion rate from the transaction
148 -- currency to the pool currency.
149 -- API caller responsible to get the conversion rate
150 -- from the original transaction when add back the
151 -- subsidy amount to the pool.
152 -- Please see 11) for the example.
153 --
154 -- 13) SUBSIDY_POOL_CURRENCY_CODE: pool currency.
155 -- API caller responsible to get the pool currency
156 -- from the original transaction when add back the
157 -- subsidy amount to the pool.
158 -- Please see 11) for the example.
159 --
160 -- Version : 1.0
161 -- History : 01-FEB-2005 SJALASUT created
162 -- 07-July-2005 cklee Added more details information for
163 -- the API
164 -- End of comments
165 PROCEDURE create_pool_transaction(p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 p_sixv_rec IN sixv_rec_type,
171 x_sixv_rec OUT NOCOPY sixv_rec_type);
172
173 PROCEDURE create_pool_transaction(p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2,
178 p_sixv_tbl IN sixv_tbl_type,
179 x_sixv_tbl OUT NOCOPY sixv_tbl_type);
180
181 END okl_subsidy_pool_trx_pvt;