1 PACKAGE okl_auto_cash_appl_rules_pvt AS
2 /* $Header: OKLRACUS.pls 120.17 2008/01/08 12:13:13 asawanka ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL VARIABLES
5 ---------------------------------------------------------------------------
6
7 G_APP_NAME CONSTANT VARCHAR2(3) := Okl_api.G_APP_NAME;
8 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_UNEXPECTED_ERROR';
9 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
10 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
11
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_AUTO_CASH_APPL_RULES_PVT';
13 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
14 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := Okl_Api.G_PARENT_TABLE_TOKEN;
15 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := Okl_Api.G_CHILD_TABLE_TOKEN;
16 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKL_NO_PARENT_RECORD';
17 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
18 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20
21 ---------------------------------------------------------------------------
22 -- GLOBAL DATASTRUCTURES
23 ---------------------------------------------------------------------------
24
25
26
27 TYPE okl_rcpt_dtls_rec_type IS RECORD ( invoice_id NUMBER DEFAULT NULL
28 ,invoice_number RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL
29 ,invoice_currency_code VARCHAR2(30)
30 ,invoice_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE DEFAULT NULL
31 ,invoice_line_number RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER%TYPE DEFAULT NULL
32 ,amount_applied AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
33 ,amount_applied_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL
34 ,trans_to_receipt_rate NUMBER
35 );
36
37 TYPE okl_rcpt_dtls_tbl_type IS TABLE OF okl_rcpt_dtls_rec_type
38 INDEX BY BINARY_INTEGER;
39
40 TYPE okl_inv_hdr_rec_type IS RECORD ( invoice_id NUMBER DEFAULT NULL
41 ,invoice_number RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL
42 ,invoice_currency_code AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
43 ,freight_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
44 ,charges_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
45 ,amount_applied AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
46 ,amount_applied_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
47 ,gl_date DATE DEFAULT NULL
48 ,trans_to_receipt_rate NUMBER
49 );
50
51 TYPE okl_inv_line_rec_type IS RECORD ( invoice_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE DEFAULT NULL
52 ,invoice_line_number RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER%TYPE DEFAULT NULL
53 ,amount_applied AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
54 ,amount_applied_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL
55 ,original_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL
56 ,line_applied AR_RECEIVABLE_APPLICATIONS_ALL.LINE_APPLIED%TYPE DEFAULT NULL
57 ,tax_applied AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE DEFAULT NULL
58 ,trans_to_receipt_rate NUMBER
59 );
60 TYPE okl_inv_line_tbl_type IS TABLE OF okl_inv_line_rec_type
61 INDEX BY BINARY_INTEGER;
62
63 TYPE okl_appl_dtls_rec_type IS RECORD ( inv_hdr_rec okl_inv_hdr_rec_type
64 ,inv_lines_tbl okl_inv_line_tbl_type
65 );
66 TYPE okl_appl_dtls_tbl_type IS TABLE OF okl_appl_dtls_rec_type
67 INDEX BY BINARY_INTEGER;
68
69 ---------------------------------------------------------------------------
70 -- Procedures and Functions
71 ---------------------------------------------------------------------------
72
73 --START: Bug 6275659 by nikshah
74 ---------------------------------------------------------------------------
75 --Get default cash application rule
76 ---------------------------------------------------------------------------
77 PROCEDURE get_default_cash_app_rule(p_org_id IN OKL_CASH_ALLCTN_RLS.ORG_ID%TYPE,
78 x_dflt_cat_id OUT NOCOPY OKL_CASH_ALLCTN_RLS.ID%TYPE,
79 x_dflt_tolerance OUT NOCOPY OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE,
80 x_dflt_days_past_quote_valid OUT NOCOPY OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE,
81 x_dflt_months_to_bill_ahead OUT NOCOPY OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE,
82 x_dflt_under_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE,
83 x_dflt_over_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE ,
84 x_dflt_receipt_msmtch OUT NOCOPY OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE
85 );
86 --END: Bug 6275659 by nikshah
87
88 -- Begin - Bug 6275659 -varangan - receipts project
89 ---------------------------------------------------------------------------
90 -- Procedures for AR invoice Number - cash application rules
91 ---------------------------------------------------------------------------
92 PROCEDURE auto_cashapp_for_arinv (
93 p_api_version IN NUMBER
94 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
95 ,x_return_status OUT NOCOPY VARCHAR2
96 ,x_msg_count OUT NOCOPY NUMBER
97 ,x_msg_data OUT NOCOPY VARCHAR2
98 ,p_customer_num IN VARCHAR2 DEFAULT NULL
99 ,p_arinv_number IN VARCHAR2 DEFAULT NULL
100 ,p_currency_code IN VARCHAR2
101 ,p_amount_app_to IN NUMBER DEFAULT NULL
102 ,p_amount_app_from IN NUMBER DEFAULT NULL
103 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
104 ,p_receipt_date IN DATE
105 ,p_arinv_id IN NUMBER DEFAULT NULL
106 ,p_org_id IN Number
107 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
108 ,x_onacc_amount OUT NOCOPY NUMBER
109 ,x_unapply_amount OUT NOCOPY NUMBER
110 );
111 -- End - Bug 6275659 - varangan - Receipts project
112
113 --START: Bug 6275659 by nikshah
114 --Get application details table for the given receipt table
115 --So the application details table will be table of invoice header
116 --and its corresponding invoice lines with its applied amount
117 PROCEDURE GET_APPLICATIONS ( p_rcpt_tbl IN okl_rcpt_dtls_tbl_type
118 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type);
119 --END: Bug 6275659 by nikshah
120
121
122 --START: Bug 6275659 by nikshah
123 --Identifies all invoice lines to be applied against for a given contract number
124 --based on CAR setup for the contract
125 PROCEDURE auto_cashapp_for_contract(p_api_version IN NUMBER
126 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
127 ,x_return_status OUT NOCOPY VARCHAR2
128 ,x_msg_count OUT NOCOPY NUMBER
129 ,x_msg_data OUT NOCOPY VARCHAR2
130 ,p_customer_num IN VARCHAR2 DEFAULT NULL
131 ,p_contract_num IN VARCHAR2 DEFAULT NULL
132 ,p_currency_code IN VARCHAR2
133 ,p_amount_app_to IN NUMBER DEFAULT NULL
134 ,p_amount_app_from IN NUMBER DEFAULT NULL
135 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
136 ,p_receipt_date IN DATE
137 ,p_org_id IN NUMBER
138 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
139 ,x_onacc_amount OUT NOCOPY NUMBER
140 ,x_unapply_amount OUT NOCOPY NUMBER
141 ) ;
142
143 --Receipt mismatch which will identify all the invoice lines
144 --for the given customer based on CAR setup i.e. Newest invoices or Oldest invoices
145 PROCEDURE receipt_mismatch(p_api_version IN NUMBER
146 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
147 ,x_return_status OUT NOCOPY VARCHAR2
148 ,x_msg_count OUT NOCOPY NUMBER
149 ,x_msg_data OUT NOCOPY VARCHAR2
150 ,p_customer_num IN VARCHAR2 DEFAULT NULL
151 ,p_currency_code IN VARCHAR2
152 ,p_rcpt_amount IN NUMBER
153 ,p_org_id IN NUMBER
154 ,p_receipt_date IN DATE
155 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
156 ,x_onacc_amount OUT NOCOPY NUMBER
157 );
158 --END: Bug 6275659 by nikshah
159 /* sosharma 30- Jul-2007
160 Identifies all invoice lines to be applied against for a given consolidated invoice number
161 based on CAR setup */
162
163 PROCEDURE auto_cashapp_for_consinv ( p_api_version IN NUMBER
164 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 ,p_customer_num IN VARCHAR2 DEFAULT NULL
169 ,p_cons_inv IN VARCHAR2
170 ,p_currency_code IN VARCHAR2
171 ,p_amount_app_to IN NUMBER DEFAULT NULL
172 ,p_amount_app_from IN NUMBER DEFAULT NULL
173 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
174 ,p_receipt_date IN DATE
175 ,p_org_id IN NUMBER
176 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
177 ,x_onacc_amount OUT NOCOPY NUMBER
178 ,x_unapply_amount OUT NOCOPY NUMBER
179 );
180
181 --Gets the rounded amount for the given amount and currency code
182 FUNCTION GET_ROUNDED_AMOUNT( p_amount_to_round IN NUMBER
183 ,p_currency_code IN VARCHAR2)
184 RETURN NUMBER;
185
186 END OKL_AUTO_CASH_APPL_RULES_PVT;
187
[Home] [Help]