[Home] [Help]
292: ,apla.expenditure_organization_id
293: ,apla.expenditure_item_date
294: /* End 5763527 */
295: FROM JAI_AP_MATCH_ERS_T temp
296: ,ap_invoice_lines_all apla -- 5763527
297: WHERE temp.invoice_id NOT IN
298: (SELECT invoice_id
299: FROM JAI_AP_MATCH_ERS_T
300: WHERE error_flag = 'Y'
540: FROM ap_invoices_all
541: WHERE invoice_id = v_invoice_id;
542:
543: Cursor get_discard_info (ln_invoice_id number) is /*Added by nprashar for bug # 12832424*/
544: Select nvl(discarded_flag,'N') from ap_invoice_lines_all
545: where invoice_id = ln_invoice_id
546: and line_number = pn_invoice_line_number
547: and po_distribution_id = po_dist_id;
548:
729:
730: /*bug 9346307*/
731: CURSOR c_po_details IS
732: SELECT po_line_id, po_line_location_id
733: FROM ap_invoice_lines_all
734: WHERE invoice_id = inv_id
735: AND line_number = pn_invoice_line_number;
736:
737: CURSOR c_ap_dist(cp_invoice_id NUMBER, cp_line_num NUMBER) IS
790: ,pa_addition_flag
791: /* End 5763527 */
792: ,a.dist_match_type --Added by nprashar for bug # 12832424
793: FROM ap_invoice_distributions_all a,
794: ap_invoice_lines_all b
795: WHERE a.invoice_id = cp_invoice_id
796: AND a.invoice_line_number = cp_line_num
797: AND b.invoice_id = cp_invoice_id
798: AND b.line_number = cp_line_num
802: r_ap_dist c_ap_dist%ROWTYPE;
803: v_tax_amt_dist NUMBER;
804:
805:
806: ln_po_line_id ap_invoice_lines_all.po_line_id%TYPE;
807: ln_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
808: ln_po_dist_id ap_invoice_lines_all.po_distribution_id%TYPE;
809: /*end bug 9346307*/
810:
803: v_tax_amt_dist NUMBER;
804:
805:
806: ln_po_line_id ap_invoice_lines_all.po_line_id%TYPE;
807: ln_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
808: ln_po_dist_id ap_invoice_lines_all.po_distribution_id%TYPE;
809: /*end bug 9346307*/
810:
811: /* Added by LGOPALSa. Bug 4210102.
804:
805:
806: ln_po_line_id ap_invoice_lines_all.po_line_id%TYPE;
807: ln_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
808: ln_po_dist_id ap_invoice_lines_all.po_distribution_id%TYPE;
809: /*end bug 9346307*/
810:
811: /* Added by LGOPALSa. Bug 4210102.
812: * Added CVD and Customs Education Cess
1352: --> Cursor to fetch the maximum line_number for current invoice, Bug# 4445989
1353: CURSOR cur_get_max_line_number
1354: IS
1355: SELECT max (line_number)
1356: FROM ap_invoice_lines_all
1357: WHERE invoice_id = inv_id;
1358:
1359: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1360: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1355: SELECT max (line_number)
1356: FROM ap_invoice_lines_all
1357: WHERE invoice_id = inv_id;
1358:
1359: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1360: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1361: IS
1362: SELECT accounting_date
1363: ,period_name
1356: FROM ap_invoice_lines_all
1357: WHERE invoice_id = inv_id;
1358:
1359: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1360: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1361: IS
1362: SELECT accounting_date
1363: ,period_name
1364: ,deferred_acctg_flag
1367: ,def_acctg_number_of_periods
1368: ,def_acctg_period_type
1369: ,set_of_books_id
1370: ,wfapproval_status -- Bug 4863208
1371: FROM ap_invoice_lines_all
1372: WHERE invoice_id = inv_id
1373: AND line_number = cpn_max_line_num;
1374:
1375: -- Added by Jason Liu for retroactive price on 2008/01/09
1382: -----------------------------------------------------------
1383: rec_max_ap_lines_all CUR_GET_MAX_AP_INV_LINE%ROWTYPE;
1384:
1385: ln_max_lnno NUMBER;
1386: ln_inv_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
1387: ln_user_id NUMBER;
1388: ln_login_id NUMBER;
1389: lv_misc VARCHAR2 (15);
1390: lv_match_type VARCHAR2(15);
1582: Fnd_File.put_line(Fnd_File.LOG, 'Start of function apportion_tax_4_price_cor_inv');
1583:
1584: begin
1585: execute immediate 'select /* price_correct_inv_id */, po_distribution_id, amount, quantity_invoiced
1586: from ap_invoice_lines_all
1587: where invoice_id = :inv_id
1588: and line_number = :inv_line_num'
1589: into cur_price_correct_inv_id, cur_po_distribution_id, cur_amount, cur_quantity_invoiced
1590: USING inv_id, pn_invoice_line_number; -- Using pn_invoice_line_number instead of dist_line_no for Bug#4445989
2016: part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
2017: cursor get_ven_info.
2018:
2019: 47 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
2020: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
2021: Resolution:- Code modified to consider ap_invoice_lines_all instead of
2022: ap_invoice_distributions_all.
2023: Following changes done
2024: - Code refering to ap_invoice_distributions_all modified to consider
2017: cursor get_ven_info.
2018:
2019: 47 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
2020: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
2021: Resolution:- Code modified to consider ap_invoice_lines_all instead of
2022: ap_invoice_distributions_all.
2023: Following changes done
2024: - Code refering to ap_invoice_distributions_all modified to consider
2025: invoice_id and invoice_line_number as unique combination
2022: ap_invoice_distributions_all.
2023: Following changes done
2024: - Code refering to ap_invoice_distributions_all modified to consider
2025: invoice_id and invoice_line_number as unique combination
2026: - invoice line record is created in ap_invoice_lines_all where ever previously
2027: distribution was created
2028: - Obsoleted JAI_CMN_FA_INV_DIST_ALL
2029: - Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
2030: and also the fields from JAI_CMN_FA_INV_DIST_ALL
2044: 51. 09-Jan-2008 Modifed by Jason Liu for retroactive price
2045:
2046: 52. 04-Feb-2008 JMEENA for bug#6780154
2047:
2048: Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
2049:
2050:
2051: 53. 21-Feb-2008 JMEENA for bug#6835548
2052: Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
2268: sysdate /* Bug 16220700 */);
2269: End Loop;
2270:
2271:
2272: Update ap_invoice_lines_all
2273: set discarded_flag = 'Y',
2274: amount = 0,
2275: last_update_date = sysdate,
2276: last_updated_by = -1
2392: sysdate,
2393: ln_user_id, /* Bug 16220700 */
2394: sysdate /* Bug 16220700 */);
2395:
2396: Update ap_invoice_lines_all
2397: set discarded_flag = 'Y',
2398: amount = 0,
2399: last_update_date = sysdate,
2400: last_updated_by = -1
2494: set last_update_date = last_update_date
2495: where invoice_id = inv_id;
2496:
2497: -- Added by Brathod for Bug# 4445989
2498: update ap_invoice_lines_all
2499: set last_update_date = last_update_date
2500: where invoice_id = inv_id;
2501: -- Cursor to select maximum line number from invoice lines for particular invoice
2502: SELECT max(line_number)
2500: where invoice_id = inv_id;
2501: -- Cursor to select maximum line number from invoice lines for particular invoice
2502: SELECT max(line_number)
2503: INTO ln_inv_line_num
2504: FROM ap_invoice_lines_all
2505: WHERE invoice_id = inv_id;
2506: -- End of Bug# 4445989
2507:
2508: /*
3251: --ln_inv_line_num := ln_inv_line_num + 1; commented out by eric for inclusive tax
3252:
3253: -- Added by Brathod for Bug#4445989
3254: Fnd_File.put_line(Fnd_File.LOG,
3255: 'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
3256:
3257: --insert exclusive tax to the ap tables
3258: --or insert recoverable inclusive tax with project information the ap tables
3259:
3278: THEN
3279: ln_inv_line_num := ln_inv_line_num + 1;
3280: --Should add ln_inv_line_num here, when it really generate invoice line.
3281: --Added by Qinglei for bug#13696344
3282: INSERT INTO ap_invoice_lines_all
3283: (
3284: INVOICE_ID
3285: , LINE_NUMBER
3286: , LINE_TYPE_LOOKUP_CODE
3555: IF NVL(i.inc_tax_flag,'N') = 'Y' and NVL(lv_modvat_flag,'N') = 'Y' THEN
3556: ln_inv_line_num := ln_inv_line_num + 1;
3557: END IF;
3558: --For inclusive tax, should ln_inv_line_num here to ensure the column invoice_line_number
3559: --is the same as line number of ap_invoice_lines_all.
3560: --Added by Qinglei on 10-Feb-2012 for bug#13589613 end
3561:
3562: INSERT INTO JAI_AP_MATCH_INV_TAXES
3563: (
3645: , DECODE ( NVL(i.inc_tax_flag,'N')
3646: , 'N',ln_inv_line_num
3647: , 'Y',DECODE(lv_modvat_flag,'Y',ln_inv_line_num,'N',pn_invoice_line_number)
3648: )
3649: --For inclusive tax recoverable line, the invoice_line_number should the same as MISC line of ap_invoice_lines_all.
3650: --To ensure that it can generate journal entries in gl interface after invoice create accounting.
3651: --Modified by Qinglei on 10-Feb-2012 for bug#13589613 begin
3652:
3653: , NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
3793: --For inclusive tax recoverable part, the positive invoice line should use invoice_line_number
3794: --when insert jai_ap_match_inv_taxes used; for the negative line, will use ln_inv_line_num + 1 as
3795: --line number.
3796: --Modified by Qinglei on 10-Feb-2012 for bug#13589613 end
3797: Fnd_File.put_line(Fnd_File.LOG,'Before inserting Inclusive Service tax lines for POT into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
3798:
3799: INSERT INTO ap_invoice_lines_all
3800: (
3801: INVOICE_ID
3795: --line number.
3796: --Modified by Qinglei on 10-Feb-2012 for bug#13589613 end
3797: Fnd_File.put_line(Fnd_File.LOG,'Before inserting Inclusive Service tax lines for POT into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
3798:
3799: INSERT INTO ap_invoice_lines_all
3800: (
3801: INVOICE_ID
3802: , LINE_NUMBER
3803: , LINE_TYPE_LOOKUP_CODE
4565: -- End 5763527
4566: --
4567:
4568: Fnd_File.put_line(Fnd_File.LOG,
4569: 'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
4570:
4571:
4572: --insert exclusive tax to the ap tables
4573: --or insert recoverable inclusive tax with project information the ap tables
4586: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
4587: ---------------------------------------------------------------------------
4588: --added by Eric for inclusive tax on 20-dec-2007,end
4589:
4590: INSERT INTO ap_invoice_lines_all
4591: (
4592: INVOICE_ID
4593: , LINE_NUMBER
4594: , LINE_TYPE_LOOKUP_CODE
5451: --added by Eric for inclusive tax on 20-dec-2007,end
5452:
5453:
5454:
5455: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'
5456: || ln_inv_line_num );
5457:
5458:
5459: --insert exclusive tax to the ap tables
5473: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
5474: ---------------------------------------------------------------------------
5475: --added by Eric for inclusive tax on 20-dec-2007,end
5476:
5477: INSERT INTO ap_invoice_lines_all
5478: (
5479: INVOICE_ID
5480: , LINE_NUMBER
5481: , LINE_TYPE_LOOKUP_CODE
6479: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
6480: ---------------------------------------------------------------------------
6481: --added by Eric for inclusive tax on 20-dec-2007,end
6482:
6483: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
6484:
6485: INSERT INTO ap_invoice_lines_all
6486: (
6487: INVOICE_ID
6481: --added by Eric for inclusive tax on 20-dec-2007,end
6482:
6483: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
6484:
6485: INSERT INTO ap_invoice_lines_all
6486: (
6487: INVOICE_ID
6488: , LINE_NUMBER
6489: , LINE_TYPE_LOOKUP_CODE
6938: IF inclu_Serv_line =2 THEN
6939: ln_inv_line_num := ln_inv_line_num + 1;
6940: END IF;
6941: --Modified by Qinglei on 10-Feb-2012 for bug#13589613 end
6942: Fnd_File.put_line(Fnd_File.LOG,'Before inserting Inclusive Service tax lines for POT into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
6943:
6944: INSERT INTO ap_invoice_lines_all
6945: (
6946: INVOICE_ID
6940: END IF;
6941: --Modified by Qinglei on 10-Feb-2012 for bug#13589613 end
6942: Fnd_File.put_line(Fnd_File.LOG,'Before inserting Inclusive Service tax lines for POT into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
6943:
6944: INSERT INTO ap_invoice_lines_all
6945: (
6946: INVOICE_ID
6947: , LINE_NUMBER
6948: , LINE_TYPE_LOOKUP_CODE
7285: --> Cursor to fetch the maximum line_number for current invoice, Bug# 4445989
7286: CURSOR cur_get_max_line_number
7287: IS
7288: SELECT max (line_number)
7289: FROM ap_invoice_lines_all
7290: WHERE invoice_id = inv_id;
7291:
7292: ln_min_dist_line_num NUMBER;
7293: lv_match_type varchar2(15);
7291:
7292: ln_min_dist_line_num NUMBER;
7293: lv_match_type varchar2(15);
7294: ln_max_lnno NUMBER;
7295: ln_inv_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
7296: ln_user_id NUMBER;
7297: ln_login_id NUMBER;
7298: lv_misc VARCHAR2 (15);
7299: -- Bug 7249100. Added by Lakshmi Gopalsami
7298: lv_misc VARCHAR2 (15);
7299: -- Bug 7249100. Added by Lakshmi Gopalsami
7300: lv_dist_class VARCHAR2(30);
7301:
7302: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
7303: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
7304: IS
7305: SELECT accounting_date
7306: ,period_name
7299: -- Bug 7249100. Added by Lakshmi Gopalsami
7300: lv_dist_class VARCHAR2(30);
7301:
7302: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
7303: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
7304: IS
7305: SELECT accounting_date
7306: ,period_name
7307: ,deferred_acctg_flag
7310: ,def_acctg_number_of_periods
7311: ,def_acctg_period_type
7312: ,set_of_books_id
7313: , wfapproval_status
7314: FROM ap_invoice_lines_all
7315: WHERE invoice_id = inv_id
7316: AND line_number = cpn_max_line_num;
7317:
7318: rec_max_ap_lines_all CUR_GET_MAX_AP_INV_LINE%ROWTYPE;
8105: part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
8106: cursor get_ven_info.
8107:
8108: 44 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
8109: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
8110: Resolution:- Code modified to consider ap_invoice_lines_all instead of
8111: ap_invoice_distributions_all.
8112: Following changes done
8113: - Code refering to ap_invoice_distributions_all modified to consider
8106: cursor get_ven_info.
8107:
8108: 44 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
8109: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
8110: Resolution:- Code modified to consider ap_invoice_lines_all instead of
8111: ap_invoice_distributions_all.
8112: Following changes done
8113: - Code refering to ap_invoice_distributions_all modified to consider
8114: invoice_id and invoice_line_number as unique combination
8111: ap_invoice_distributions_all.
8112: Following changes done
8113: - Code refering to ap_invoice_distributions_all modified to consider
8114: invoice_id and invoice_line_number as unique combination
8115: - invoice line record is created in ap_invoice_lines_all where ever previously
8116: distribution was created
8117: - Obsoleted JAI_CMN_FA_INV_DIST_ALL
8118: - Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
8119: and also the fields from JAI_CMN_FA_INV_DIST_ALL
8123: 1) Commented the cursor - for_acct_id and corresponding open/fetch/close for the same.
8124: 2) Commented the call to function - jai_general_pkg.get_accounting_method
8125:
8126: 46. 08-Dec-2005 Bug 4863208. Added by Lakshmi Gopalsami Version 120.2
8127: (1) Added wfapproval_Status in insert to ap_invoice_lines_all
8128: (2) Derived the org_id for inserting into ap_invoice_distributions_all
8129: This is added in CURSOR for_dist_insertion
8130: (3) Added values for match_status_flag, reversal_flag in insert
8131:
8130: (3) Added values for match_status_flag, reversal_flag in insert
8131:
8132: 47. 04-Feb-2008 JMEENA for bug#6780154
8133:
8134: Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
8135:
8136:
8137: 48. 21-Feb-2008 JMEENA for bug#6835548
8138: Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
8267: BEGIN --06-Mar-2002
8268:
8269:
8270: -- Cursor to select maximum line number from invoice lines for particular invoice
8271: -- Lock ap_invoice_lines_all
8272: UPDATE ap_invoice_lines_all
8273: SET last_update_date = last_update_date
8274: WHERE invoice_id = inv_id;
8275:
8268:
8269:
8270: -- Cursor to select maximum line number from invoice lines for particular invoice
8271: -- Lock ap_invoice_lines_all
8272: UPDATE ap_invoice_lines_all
8273: SET last_update_date = last_update_date
8274: WHERE invoice_id = inv_id;
8275:
8276: SELECT max(line_number)
8274: WHERE invoice_id = inv_id;
8275:
8276: SELECT max(line_number)
8277: INTO ln_inv_line_num
8278: FROM ap_invoice_lines_all
8279: WHERE invoice_id = inv_id;
8280: -- End of Bug# 4445989
8281: /*
8282: Commented by Brathod for Bug# 4445989
8824: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
8825: ---------------------------------------------------------------------------
8826: --added by Eric for inclusive tax on 20-dec-2007,end
8827:
8828: INSERT INTO ap_invoice_lines_all
8829: (
8830: INVOICE_ID
8831: , LINE_NUMBER
8832: , LINE_TYPE_LOOKUP_CODE
9690: ---------------------------------------------------------------------------
9691: --added by Eric for inclusive tax on 20-dec-2007,end
9692:
9693:
9694: INSERT INTO ap_invoice_lines_all
9695: (
9696: INVOICE_ID
9697: , LINE_NUMBER
9698: , LINE_TYPE_LOOKUP_CODE
10526: --added by Eric for inclusive tax on 20-dec-2007,end
10527:
10528:
10529: Fnd_File.put_line(Fnd_File.LOG,
10530: 'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
10531:
10532: --insert exclusive tax to the ap tables
10533: --or insert recoverable inclusive tax with project information the ap tables
10534:
10546: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
10547: ---------------------------------------------------------------------------
10548: --added by Eric for inclusive tax on 20-dec-2007,end
10549:
10550: INSERT INTO ap_invoice_lines_all
10551: (
10552: INVOICE_ID
10553: , LINE_NUMBER
10554: , LINE_TYPE_LOOKUP_CODE