[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)
51: P_reprocess IN varchar2
52: )
53: is
54:
55: cursor c_get_distrib_tax_details (p_invoice_id number, p_line_number number) IS /* p_distribution_no --uptake of ap_invoice_lines_all */
56: select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
57: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
58: ,recoverable_flag -- 5763527
59: from JAI_AP_MATCH_INV_TAXES
53: is
54:
55: cursor c_get_distrib_tax_details (p_invoice_id number, p_line_number number) IS /* p_distribution_no --uptake of ap_invoice_lines_all */
56: select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
57: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
58: ,recoverable_flag -- 5763527
59: from JAI_AP_MATCH_INV_TAXES
60: where invoice_id = p_invoice_id
61: and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
57: parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
58: ,recoverable_flag -- 5763527
59: from JAI_AP_MATCH_INV_TAXES
60: where invoice_id = p_invoice_id
61: and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
62: /* and distribution_line_number = p_distribution_no;*/
63:
64: cursor c_get_tax_details (p_tax_id number) is
65: select tax_type, tax_rate, stform_type
117: select match_option, ship_to_organization_id, ship_to_location_id
118: from po_line_locations_all
119: where line_location_id = p_po_line_location_id;
120:
121: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
122: 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
123: select rcv_transaction_id
124: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
125: where invoice_id = p_invoice_id
118: from po_line_locations_all
119: where line_location_id = p_po_line_location_id;
120:
121: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
122: 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
123: select rcv_transaction_id
124: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
125: where invoice_id = p_invoice_id
126: and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
120:
121: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
122: 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
123: select rcv_transaction_id
124: from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
125: where invoice_id = p_invoice_id
126: and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
127: and po_distribution_id = p_po_distribution_id;
128:
193: lv_lt_inclu_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type ; -- Added by Jia for Bug#9535954
194: v_parent_invoice_line_number JAI_AP_MATCH_INV_TAXES.parent_invoice_line_number%type ;
195:
196:
197: function getSTformsTaxBaseAmount /* uptake of ap_invoice_lines_all*/
198: (
199: p_invoice_id number,
200: p_line_number number, --p_invoice_distribution_id number,
201: p_tax_id number,
226: v_exchange_rate ap_invoices_all.exchange_rate%type;
227:
228: v_invoice_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type;
229:
230: v_line_number ap_invoice_lines_all.line_number%type ;
231: v_match_type ap_invoice_lines_all.match_type%type; /*Bug 14277151*/
232:
233: Type v_prec_check is table of number index by binary_integer; /*Added by nprashar for bug # 9830853*/
234: p_prec_check v_prec_check;
227:
228: v_invoice_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type;
229:
230: v_line_number ap_invoice_lines_all.line_number%type ;
231: v_match_type ap_invoice_lines_all.match_type%type; /*Bug 14277151*/
232:
233: Type v_prec_check is table of number index by binary_integer; /*Added by nprashar for bug # 9830853*/
234: p_prec_check v_prec_check;
235:
237: select set_of_books_id, invoice_currency_code,exchange_date, exchange_rate_type, exchange_rate
238: from ap_invoices_all
239: where invoice_id = p_invoice_id;
240:
241: /* uptake of ap_invoice_lines_all */
242: /*Bug 14277151 - Added Match Type*/
243: cursor c_get_inv_dist_details is
244: select line_number, po_distribution_id, rcv_transaction_id, amount, match_type /* invoice_distribution_id*/
245: from ap_invoice_lines_all --ap_invoice_distributions_all
241: /* uptake of ap_invoice_lines_all */
242: /*Bug 14277151 - Added Match Type*/
243: cursor c_get_inv_dist_details is
244: select line_number, po_distribution_id, rcv_transaction_id, amount, match_type /* invoice_distribution_id*/
245: from ap_invoice_lines_all --ap_invoice_distributions_all
246: where invoice_id = p_invoice_id
247: and line_number = p_line_number ;
248: /* and invoice_distribution_id = p_invoice_distribution_id ; */
249:
276: WHERE transaction_id = p_rcv_transaction_id)
277: AND tax_id = p_tax_id;
278: /*Bug 14277151 - End*/
279:
280: /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
281: cursor c_get_non_zero_precedence_amt
282: (
283: p_precedence number,
284: /* p_precedence_2 number,
292: p_parent_line_number number
293: )
294: is
295: select amount
296: from ap_invoice_lines_all
297: where invoice_id = p_invoice_id
298: and line_number =
299: ( select jmit.invoice_line_number
300: from JAI_AP_MATCH_INV_TAXES jmit
705:
706: lv_lt_lookup_code := 'MISCELLANEOUS' ;
707: lv_lt_inclu_lookup_code := 'ITEM' ;
708:
709: /* uptake of ap_invoice_lines_all */
710: for c_inv_distributions in
711: (
712: select invoice_id,
713: line_number, --distribution_line_number
713: line_number, --distribution_line_number
714: --invoice_distribution_id,
715: po_distribution_id,
716: rcv_transaction_id
717: from ap_invoice_lines_all b --ap_invoice_distributions_all b
718: where invoice_id = c_invoices.invoice_id
719: and line_type_lookup_code = lv_lt_lookup_code --'MISCELLANEOUS' -- only tax lines /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
720: /* and nvl(reversal_flag, 'N') <> 'Y' */
721: /*Commented this condition for bug # 12561167 and po_distribution_id is not null*/
742: select invoice_id,
743: line_number,
744: po_distribution_id,
745: rcv_transaction_id
746: from ap_invoice_lines_all b
747: where invoice_id = c_invoices.invoice_id
748: and line_type_lookup_code = lv_lt_inclu_lookup_code
749: and po_distribution_id is not null
750: and not exists
783: v_po_line_location_id := null;
784: v_po_header_id := null;
785: v_tax_amount := null;
786:
787: /* uptake of ap_invoice_lines_all */
788: open c_get_distrib_tax_details( c_inv_distributions.invoice_id,
789: c_inv_distributions.line_number);
790: --c_inv_distributions.distribution_line_number);
791:
905: fetch c_get_tax_ln_no_receipt into v_tax_line_no;
906: close c_get_tax_ln_no_receipt;
907: end if;
908:
909: /* uptake of ap_invoice_lines_all */
910: v_tax_target_amount :=
911: getSTformsTaxBaseAmount
912: (
913: c_inv_distributions.invoice_id,