[Home] [Help]
PACKAGE BODY: APPS.GMF_AR_GET_INVOICE_IDS
Source
1 PACKAGE BODY GMF_AR_GET_INVOICE_IDS AS
2 /* $Header: gmfinvdb.pls 115.1 2002/11/11 00:39:12 rseshadr ship $ */
3 CURSOR cur_get_invoice_ids(
4 sales_order_no VARCHAR2,
5 t_start_date DATE,
6 t_end_date DATE) IS
7 SELECT
8 rac.CUSTOMER_TRX_ID, rac.LAST_UPDATED_BY, rac.LAST_UPDATE_DATE,
9 rac.CREATION_DATE, rac.CREATED_BY, rac.LAST_UPDATE_LOGIN,
10 rac.TRX_NUMBER, rac.CUST_TRX_TYPE_ID, rac.TRX_DATE,
11 rac.SET_OF_BOOKS_ID, BILL_TO_CONTACT_ID, BATCH_ID,
12 BATCH_SOURCE_ID, rac.REASON_CODE, SOLD_TO_CUSTOMER_ID,
13 SOLD_TO_CONTACT_ID, SOLD_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID,
14 BILL_TO_SITE_USE_ID, SHIP_TO_CUSTOMER_ID, SHIP_TO_CONTACT_ID,
15 SHIP_TO_SITE_USE_ID, REMIT_TO_ADDRESS_ID, rac.TERM_ID,
16 TERM_DUE_DATE, rac.PREVIOUS_CUSTOMER_TRX_ID, rac.PRIMARY_SALESREP_ID,
17 PRINTING_ORIGINAL_DATE, PRINTING_LAST_PRINTED, rac.PRINTING_OPTION,
18 PRINTING_COUNT, PRINTING_PENDING, rac.PURCHASE_ORDER,
19 rac.PURCHASE_ORDER_REVISION, rac.PURCHASE_ORDER_DATE,CUSTOMER_REFERENCE,
20 rac.INTERNAL_NOTES, rac.EXCHANGE_RATE_TYPE, rac.EXCHANGE_DATE,
21 rac.EXCHANGE_RATE, rac.TERRITORY_ID, rac.INVOICE_CURRENCY_CODE,
22 rac.INITIAL_CUSTOMER_TRX_ID, rac.AGREEMENT_ID, rac.END_DATE_COMMITMENT,
23 rac.START_DATE_COMMITMENT, rac.LAST_PRINTED_SEQUENCE_NUM,
24 rac.ORIG_SYSTEM_BATCH_NAME,
25 rac.POST_REQUEST_ID, rac.REQUEST_ID, rac.PROGRAM_APPLICATION_ID,
26 rac.PROGRAM_ID, rac.PROGRAM_UPDATE_DATE, FINANCE_CHARGES,
27 COMPLETE_FLAG, POSTING_CONTROL_ID, BILL_TO_ADDRESS_ID,
28 RA_POST_LOOP_NUMBER, SHIP_TO_ADDRESS_ID, CREDIT_METHOD_FOR_RULES,
29 rac.CREDIT_METHOD_FOR_INSTALLMENTS, rac.RECEIPT_METHOD_ID,
30 rac.RELATED_CUSTOMER_TRX_ID,
31 rac.INVOICING_RULE_ID, rac.SHIP_VIA, rac.SHIP_DATE_ACTUAL,
32 rac.WAYBILL_NUMBER, rac.FOB_POINT, rac.CUSTOMER_BANK_ACCOUNT_ID,
33 STATUS_TRX, DOC_SEQUENCE_ID,
34 DOC_SEQUENCE_VALUE, rac.PAYING_CUSTOMER_ID, rac.PAYING_SITE_USE_ID,
35 RELATED_BATCH_SOURCE_ID, DEFAULT_TAX_EXEMPT_FLAG,
36 CREATED_FROM
37 from RA_CUSTOMER_TRX_ALL rac
38 where rac.customer_trx_id in
39 (select distinct rat.customer_trx_id
40 from ra_customer_trx_lines_all rat
41 connect by previous_customer_trx_id = prior customer_trx_id
42 start with rat.sales_order = sales_order_no )
43 AND (rac.last_update_date between nvl(t_start_date, rac.last_update_date)
44 AND nvl(t_end_date, rac.last_update_date));
45
46 PROCEDURE get_invoice_ids
47 (sales_order_no IN OUT NOCOPY VARCHAR2,
48 invoice_id OUT NOCOPY NUMBER,
49 t_start_date IN OUT NOCOPY DATE,
50 t_end_date IN OUT NOCOPY DATE,
51 t_created_by OUT NOCOPY NUMBER,
52 t_creation_date OUT NOCOPY DATE,
53 t_last_updated_by OUT NOCOPY NUMBER,
54 t_last_update_date OUT NOCOPY DATE,
55 t_customer_trx_id OUT NOCOPY NUMBER,
56 t_last_update_login OUT NOCOPY NUMBER,
57 t_trx_number OUT NOCOPY VARCHAR2,
58 t_cust_trx_type_id OUT NOCOPY NUMBER,
59 t_trx_date OUT NOCOPY DATE,
60 t_set_of_books_id OUT NOCOPY NUMBER,
61 t_bill_to_contact_id OUT NOCOPY NUMBER,
62 t_batch_id OUT NOCOPY NUMBER,
63 t_batch_source_id OUT NOCOPY NUMBER,
64 t_reason_code OUT NOCOPY VARCHAR2,
65 t_sold_to_customer_id OUT NOCOPY NUMBER,
66 t_sold_to_contact_id OUT NOCOPY NUMBER,
67 t_sold_to_site_use_id OUT NOCOPY NUMBER,
68 t_bill_to_customer_id OUT NOCOPY NUMBER,
69 t_bill_to_site_use_id OUT NOCOPY NUMBER,
70 t_ship_to_customer_id OUT NOCOPY NUMBER,
71 t_ship_to_contact_id OUT NOCOPY NUMBER,
72 t_ship_to_site_use_id OUT NOCOPY NUMBER,
73 t_remit_to_address_id OUT NOCOPY NUMBER,
74 t_term_id OUT NOCOPY NUMBER,
75 t_term_due_date OUT NOCOPY DATE,
76 t_previous_customer_trx_id OUT NOCOPY NUMBER,
77 t_primary_salesrep_id OUT NOCOPY NUMBER,
78 t_printing_original_date OUT NOCOPY DATE,
79 t_printing_last_printed OUT NOCOPY DATE,
80 t_printing_option OUT NOCOPY VARCHAR2,
81 t_printing_count OUT NOCOPY NUMBER,
82 t_printing_pending OUT NOCOPY VARCHAR2,
83 t_purchase_order OUT NOCOPY VARCHAR2,
84 t_purchase_order_revision OUT NOCOPY VARCHAR2,
85 t_purchase_order_date OUT NOCOPY DATE,
86 t_customer_reference OUT NOCOPY VARCHAR2,
87 t_internal_notes OUT NOCOPY VARCHAR2,
88 t_exchange_rate_type OUT NOCOPY VARCHAR2,
89 t_exchange_date OUT NOCOPY DATE,
90 t_exchange_rate OUT NOCOPY NUMBER,
91 t_territory_id OUT NOCOPY NUMBER,
92 t_invoice_currency_code OUT NOCOPY VARCHAR2,
93 t_initial_customer_trx_id OUT NOCOPY NUMBER,
94 t_agreement_id OUT NOCOPY NUMBER,
95 t_end_date_commitment OUT NOCOPY DATE,
96 t_start_date_commitment OUT NOCOPY DATE,
97 t_last_printed_sequence_num OUT NOCOPY NUMBER,
98 t_orig_system_batch_name OUT NOCOPY VARCHAR2,
99 t_post_request_id OUT NOCOPY NUMBER,
100 t_request_id OUT NOCOPY NUMBER,
101 t_program_application_id OUT NOCOPY NUMBER,
102 t_program_id OUT NOCOPY NUMBER,
103 t_program_update_date OUT NOCOPY DATE,
104 t_finance_charges OUT NOCOPY VARCHAR2,
105 t_complete_flag OUT NOCOPY VARCHAR2,
106 t_posting_control_id OUT NOCOPY NUMBER,
107 t_bill_to_address_id OUT NOCOPY NUMBER,
108 t_ra_post_loop_number OUT NOCOPY NUMBER,
109 t_ship_to_address_id OUT NOCOPY NUMBER,
110 t_credit_method_for_rules OUT NOCOPY VARCHAR2,
111 t_cr_method_for_installments OUT NOCOPY VARCHAR2,
112 t_receipt_method_id OUT NOCOPY NUMBER,
113 t_related_customer_trx_id OUT NOCOPY NUMBER,
114 t_invoicing_rule_id OUT NOCOPY NUMBER,
115 t_ship_via OUT NOCOPY VARCHAR2,
116 t_ship_date_actual OUT NOCOPY DATE,
117 t_waybill_number OUT NOCOPY VARCHAR2,
118 t_fob_point OUT NOCOPY VARCHAR2,
119 t_customer_bank_account_id OUT NOCOPY NUMBER,
120 t_status_trx OUT NOCOPY VARCHAR2,
121 t_doc_sequence_id OUT NOCOPY NUMBER,
122 t_doc_sequence_value OUT NOCOPY NUMBER,
123 t_paying_customer_id OUT NOCOPY NUMBER,
124 t_paying_site_use_id OUT NOCOPY NUMBER,
125 t_related_batch_source_id OUT NOCOPY NUMBER,
126 t_default_tax_exempt_flag OUT NOCOPY VARCHAR2,
127 t_created_from OUT NOCOPY VARCHAR2,
128 row_to_fetch IN OUT NOCOPY NUMBER,
129 error_status OUT NOCOPY NUMBER ) IS
130
131 BEGIN
132 if NOT cur_get_invoice_ids%ISOPEN
133 then
134 OPEN cur_get_invoice_ids( sales_order_no, t_start_date, t_end_date);
135 end if;
136
137 FETCH cur_get_invoice_ids INTO
138 invoice_id,
139 t_last_updated_by,
140 t_last_update_date,
141 t_creation_date,
142 t_created_by,
143 t_last_update_login,
144 t_trx_number,
145 t_cust_trx_type_id,
146 t_trx_date,
147 t_set_of_books_id,
148 t_bill_to_contact_id,
149 t_batch_id,
150 t_batch_source_id,
151 t_reason_code,
152 t_sold_to_customer_id,
153 t_sold_to_contact_id,
154 t_sold_to_site_use_id,
155 t_bill_to_customer_id,
156 t_bill_to_site_use_id,
157 t_ship_to_customer_id,
158 t_ship_to_contact_id,
159 t_ship_to_site_use_id,
160 t_remit_to_address_id,
161 t_term_id,
162 t_term_due_date,
163 t_previous_customer_trx_id,
164 t_primary_salesrep_id,
165 t_printing_original_date,
166 t_printing_last_printed,
167 t_printing_option,
168 t_printing_count,
169 t_printing_pending,
170 t_purchase_order,
171 t_purchase_order_revision,
172 t_purchase_order_date,
173 t_customer_reference,
174 t_internal_notes,
175 t_exchange_rate_type,
176 t_exchange_date,
177 t_exchange_rate,
178 t_territory_id,
179 t_invoice_currency_code,
180 t_initial_customer_trx_id,
181 t_agreement_id,
182 t_end_date_commitment,
183 t_start_date_commitment,
184 t_last_printed_sequence_num,
185 t_orig_system_batch_name,
186 t_post_request_id,
187 t_request_id,
188 t_program_application_id,
189 t_program_id,
190 t_program_update_date,
191 t_finance_charges,
192 t_complete_flag,
193 t_posting_control_id,
194 t_bill_to_address_id,
195 t_ra_post_loop_number,
196 t_ship_to_address_id,
197 t_credit_method_for_rules,
198 t_cr_method_for_installments,
199 t_receipt_method_id,
200 t_related_customer_trx_id,
201 t_invoicing_rule_id,
202 t_ship_via,
203 t_ship_date_actual,
204 t_waybill_number,
205 t_fob_point,
206 t_customer_bank_account_id,
207 t_status_trx,
208 t_doc_sequence_id,
209 t_doc_sequence_value,
210 t_paying_customer_id,
211 t_paying_site_use_id,
212 t_related_batch_source_id,
213 t_default_tax_exempt_flag,
214 t_created_from;
215
216 if cur_get_invoice_ids%NOTFOUND
217 then
218 error_status := 100;
219 close cur_get_invoice_ids;
220 end if;
221
222 if row_to_fetch = 1 and cur_get_invoice_ids%ISOPEN
223 then
224 close cur_get_invoice_ids;
225 end if;
226
227 EXCEPTION
228
229 when others then
230 error_status := SQLCODE;
231 END get_invoice_ids;
232 END GMF_AR_GET_INVOICE_IDS;