[Home] [Help]
13: 13-Jun-2005 4428980 File Version: 116.3
14: Ramananda for bug#4428980. Removal of SQL LITERALs is done
15:
16: 24-Jun-2005 4454818 Ramananda for bug#4454818 File Version: 116.4
17: ST Forms Impact: Uptake of ap_invoice_lines_all
18:
19: 10-May-2006 4949400 Sanjikum for Bug#4929400, File Version 120.2
20: 1) Modified the cursor - c_get_not_validated_count
21: 2) Changed the definition of variable - v_not_validated_count from number to VARCHAR2(1)
35: P_reprocess IN varchar2
36: )
37: is
38:
39: cursor c_get_distrib_tax_details (p_invoice_id number, p_line_number number) IS /* p_distribution_no --uptake of ap_invoice_lines_all */
40: select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
41: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
42: ,recoverable_flag -- 5763527
43: from JAI_AP_MATCH_INV_TAXES
37: is
38:
39: cursor c_get_distrib_tax_details (p_invoice_id number, p_line_number number) IS /* p_distribution_no --uptake of ap_invoice_lines_all */
40: select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
41: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
42: ,recoverable_flag -- 5763527
43: from JAI_AP_MATCH_INV_TAXES
44: where invoice_id = p_invoice_id
45: and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
41: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
42: ,recoverable_flag -- 5763527
43: from JAI_AP_MATCH_INV_TAXES
44: where invoice_id = p_invoice_id
45: and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
46: /* and distribution_line_number = p_distribution_no;*/
47:
48: cursor c_get_tax_details (p_tax_id number) is
49: select tax_type, tax_rate, stform_type
101: select match_option, ship_to_organization_id, ship_to_location_id
102: from po_line_locations_all
103: where line_location_id = p_po_line_location_id;
104:
105: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
106: cursor c_get_rcv_transaction_id(p_invoice_id number, p_po_distribution_id number, cp_lt_lookup_code ap_invoice_lines_all.line_type_lookup_code%type ) is
107: select rcv_transaction_id
108: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
109: where invoice_id = p_invoice_id
102: from po_line_locations_all
103: where line_location_id = p_po_line_location_id;
104:
105: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
106: cursor c_get_rcv_transaction_id(p_invoice_id number, p_po_distribution_id number, cp_lt_lookup_code ap_invoice_lines_all.line_type_lookup_code%type ) is
107: select rcv_transaction_id
108: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
109: where invoice_id = p_invoice_id
110: and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
104:
105: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
106: cursor c_get_rcv_transaction_id(p_invoice_id number, p_po_distribution_id number, cp_lt_lookup_code ap_invoice_lines_all.line_type_lookup_code%type ) is
107: select rcv_transaction_id
108: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
109: where invoice_id = p_invoice_id
110: and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
111: and po_distribution_id = p_po_distribution_id;
112:
176: lv_lt_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type ;
177: v_parent_invoice_line_number JAI_AP_MATCH_INV_TAXES.parent_invoice_line_number%type ;
178:
179:
180: function getSTformsTaxBaseAmount /* uptake of ap_invoice_lines_all*/
181: (
182: p_invoice_id number,
183: p_line_number number, --p_invoice_distribution_id number,
184: p_tax_id number,
209: v_exchange_rate ap_invoices_all.exchange_rate%type;
210:
211: v_invoice_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type;
212:
213: v_line_number ap_invoice_lines_all.line_number%type ;
214:
215: cursor c_get_invoice_currency_dtl is
216: select set_of_books_id, invoice_currency_code,exchange_date, exchange_rate_type, exchange_rate
217: from ap_invoices_all
216: select set_of_books_id, invoice_currency_code,exchange_date, exchange_rate_type, exchange_rate
217: from ap_invoices_all
218: where invoice_id = p_invoice_id;
219:
220: /* uptake of ap_invoice_lines_all */
221: cursor c_get_inv_dist_details is
222: select line_number, po_distribution_id, rcv_transaction_id, amount /* invoice_distribution_id*/
223: from ap_invoice_lines_all --ap_invoice_distributions_all
224: where invoice_id = p_invoice_id
219:
220: /* uptake of ap_invoice_lines_all */
221: cursor c_get_inv_dist_details is
222: select line_number, po_distribution_id, rcv_transaction_id, amount /* invoice_distribution_id*/
223: from ap_invoice_lines_all --ap_invoice_distributions_all
224: where invoice_id = p_invoice_id
225: and line_number = p_line_number ;
226: /* and invoice_distribution_id = p_invoice_distribution_id ; */
227:
236: from JAI_PO_TAXES
237: where tax_id = p_tax_id
238: and line_location_id = p_po_line_location_id;
239:
240: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
241: cursor c_get_non_zero_precedence_amt
242: (
243: p_precedence_1 number,
244: p_precedence_2 number,
252: p_parent_line_number number
253: )
254: is
255: select sum(amount)
256: from ap_invoice_lines_all
257: where invoice_id = p_invoice_id
258: and line_number in
259: ( select invoice_line_number
260: from JAI_AP_MATCH_INV_TAXES
311: fetch c_get_tax_precedence into
312: v_precedence_1, v_precedence_2, v_precedence_3, v_precedence_4, v_precedence_5;
313: close c_get_tax_precedence;
314:
315: /* uptake of ap_invoice_lines_all */
316: open c_get_non_zero_precedence_amt
317: (v_precedence_1, v_precedence_2, v_precedence_3, v_precedence_4, v_precedence_5,
318: v_po_header_id, v_po_line_id, v_po_line_location_id, v_po_distribution_id,
319: v_line_number); --v_invoice_distribution_id);
594: v_error_message := null;
595:
596: lv_lt_lookup_code := 'MISCELLANEOUS' ;
597:
598: /* uptake of ap_invoice_lines_all */
599: for c_inv_distributions in
600: (
601: select invoice_id,
602: line_number, --distribution_line_number
602: line_number, --distribution_line_number
603: --invoice_distribution_id,
604: po_distribution_id,
605: rcv_transaction_id
606: from ap_invoice_lines_all b --ap_invoice_distributions_all b
607: where invoice_id = c_invoices.invoice_id
608: and line_type_lookup_code = lv_lt_lookup_code --'MISCELLANEOUS' -- only tax lines /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
609: /* and nvl(reversal_flag, 'N') <> 'Y' */
610: and po_distribution_id is not null
643: v_po_line_location_id := null;
644: v_po_header_id := null;
645: v_tax_amount := null;
646:
647: /* uptake of ap_invoice_lines_all */
648: open c_get_distrib_tax_details( c_inv_distributions.invoice_id,
649: c_inv_distributions.line_number);
650: --c_inv_distributions.distribution_line_number);
651:
765: fetch c_get_tax_ln_no_receipt into v_tax_line_no;
766: close c_get_tax_ln_no_receipt;
767: end if;
768:
769: /* uptake of ap_invoice_lines_all */
770: v_tax_target_amount :=
771: getSTformsTaxBaseAmount
772: (
773: c_inv_distributions.invoice_id,