[Home] [Help]
PACKAGE: APPS.AP_RETAINAGE_RELEASE_PKG
Source
1 Package ap_retainage_release_pkg As
2 /* $Header: apcwrels.pls 120.0 2005/06/25 00:42:39 schitlap noship $ */
3
4 TYPE release_shipments_record IS RECORD
5 (po_header_id po_line_locations_all.po_header_id%TYPE,
6 po_line_id po_line_locations_all.po_line_id%TYPE,
7 po_release_id po_line_locations_all.po_release_id%TYPE,
8 line_location_id po_line_locations_all.line_location_id%TYPE,
9 invoice_id ap_invoices_all.invoice_id%TYPE,
10 line_number ap_invoice_lines_all.line_number%TYPE,
11 release_amount number,
12 release_amount_remaining number);
13
14 TYPE release_shipments_tab IS TABLE OF release_shipments_record INDEX BY BINARY_INTEGER;
15
16 Procedure create_release (x_invoice_id IN ap_invoices_all.invoice_id%TYPE,
17 x_release_shipments_tab IN release_shipments_tab);
18 /*
19 CURSOR c_retained_lines
20 (c_line_location_id IN ap_invoice_lines_all.po_line_location_id%TYPE) Is
21 SELECT ai.invoice_currency_code,
22 ai.exchange_rate,
23 ail.invoice_id,
24 ail.line_number,
25 ail.retained_amount_remaining,
26 ail.description,
27 ail.match_type,
28 ail.set_of_books_id,
29 ail.unit_meas_lookup_code,
30 ail.unit_price,
31 ail.ussgl_transaction_code,
32 ail.po_header_id,
33 ail.po_line_id,
34 ail.po_release_id,
35 ail.po_line_location_id,
36 ail.po_distribution_id,
37 ail.rcv_transaction_id,
38 ail.final_match_flag,
39 ail.asset_book_type_code,
40 ail.asset_category_id,
41 ail.project_id,
42 ail.task_id,
43 ail.expenditure_type,
44 ail.expenditure_item_date,
45 ail.expenditure_organization_id,
46 ail.award_id,
47 ail.awt_group_id,
48 ail.reference_1,
49 ail.reference_2,
50 ail.receipt_verified_flag,
51 ail.receipt_required_flag,
52 ail.receipt_missing_flag,
53 ail.justification,
54 ail.expense_group,
55 ail.start_expense_date,
56 ail.end_expense_date,
57 ail.receipt_currency_code,
58 ail.receipt_conversion_rate,
59 ail.receipt_currency_amount,
60 ail.daily_amount,
61 ail.web_parameter_id,
62 ail.adjustment_reason,
63 ail.merchant_document_number,
64 ail.merchant_name,
65 ail.merchant_reference,
66 ail.merchant_tax_reg_number,
67 ail.merchant_taxpayer_id,
68 ail.country_of_supply,
69 ail.credit_card_trx_id,
70 ail.company_prepaid_invoice_id,
71 ail.cc_reversal_flag,
72 ail.attribute_category,
73 ail.attribute1,
74 ail.attribute2,
75 ail.attribute3,
76 ail.attribute4,
77 ail.attribute5,
78 ail.attribute6,
79 ail.attribute7,
80 ail.attribute8,
81 ail.attribute9,
82 ail.attribute10,
83 ail.attribute11,
84 ail.attribute12,
85 ail.attribute13,
86 ail.attribute14,
87 ail.attribute15,
88 ail.global_attribute_category,
89 ail.global_attribute1,
90 ail.global_attribute2,
91 ail.global_attribute3,
92 ail.global_attribute4,
93 ail.global_attribute5,
94 ail.global_attribute6,
95 ail.global_attribute7,
96 ail.global_attribute8,
97 ail.global_attribute9,
98 ail.global_attribute10,
99 ail.global_attribute11,
100 ail.global_attribute12,
101 ail.global_attribute13,
102 ail.global_attribute14,
103 ail.global_attribute15,
104 ail.global_attribute16,
105 ail.global_attribute17,
106 ail.global_attribute18,
107 ail.global_attribute19,
108 ail.global_attribute20,
109 ail.ship_to_location_id,
110 ail.primary_intended_use,
111 ail.product_fisc_classification,
112 ail.trx_business_category,
113 ail.product_type,
114 ail.product_category,
115 ail.user_defined_fisc_class,
116 ail.purchasing_category_id,
117 ail.wfapproval_status
118 FROM ap_invoices_all ai,
119 ap_invoice_lines_all ail
120 WHERE ai.invoice_id = ail.invoice_id
121 AND ail.po_line_location_id = c_line_location_id
122 AND ail.retained_amount_remaining > 0
123 AND ail.line_type_lookup_code = 'ITEM'
124 AND NVL(ail.discarded_flag,'N') <> 'Y'
125 AND NVL(ail.line_selected_for_release_flag,'N') <> 'Y'
126 ORDER BY ai.creation_date;
127 */
128
129 --
130 -- IMPORTANT NOTE:
131 -- The select columns in c_retained_lines_po and c_retained_lines_inv should always match.
132 --
133
134 CURSOR c_retained_lines_po
135 (c_line_location_id IN ap_invoice_lines_all.po_line_location_id%TYPE) Is
136 SELECT ai.invoice_currency_code,
137 ai.exchange_rate,
138 ail.*
139 FROM ap_invoices_all ai,
140 ap_invoice_lines_all ail
141 WHERE ai.invoice_id = ail.invoice_id
142 AND ail.po_line_location_id = c_line_location_id
143 AND ail.retained_amount_remaining > 0
144 AND ail.line_type_lookup_code = 'ITEM'
145 AND NVL(ail.discarded_flag,'N') <> 'Y'
146 AND NVL(ail.line_selected_for_release_flag,'N') <> 'Y'
147 ORDER BY ai.creation_date;
148
149 CURSOR c_retained_lines_inv
150 (c_invoice_id IN ap_invoice_lines_all.invoice_id%TYPE,
151 c_line_number IN ap_invoice_lines_all.line_number%TYPE) Is
152 SELECT ai.invoice_currency_code,
153 ai.exchange_rate,
154 ail.*
155 FROM ap_invoices_all ai,
156 ap_invoice_lines_all ail
157 WHERE ai.invoice_id = ail.invoice_id
158 AND ai.invoice_id = c_invoice_id
159 AND ail.line_number = c_line_number
160 ORDER BY ai.creation_date;
161
162 TYPE retainedLinesType IS TABLE OF c_retained_lines_po%rowtype INDEX BY PLS_INTEGER;
163
164 retained_lines_tab retainedLinesType;
165
166 End ap_retainage_release_pkg;