[Home] [Help]
249: ,apla.expenditure_organization_id
250: ,apla.expenditure_item_date
251: /* End 5763527 */
252: FROM JAI_AP_MATCH_ERS_T temp
253: ,ap_invoice_lines_all apla -- 5763527
254: WHERE temp.invoice_id NOT IN
255: (SELECT invoice_id
256: FROM JAI_AP_MATCH_ERS_T
257: WHERE error_flag = 'Y'
1035: --> Cursor to fetch the maximum line_number for current invoice, Bug# 4445989
1036: CURSOR cur_get_max_line_number
1037: IS
1038: SELECT max (line_number)
1039: FROM ap_invoice_lines_all
1040: WHERE invoice_id = inv_id;
1041:
1042: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1043: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1038: SELECT max (line_number)
1039: FROM ap_invoice_lines_all
1040: WHERE invoice_id = inv_id;
1041:
1042: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1043: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1044: IS
1045: SELECT accounting_date
1046: ,period_name
1039: FROM ap_invoice_lines_all
1040: WHERE invoice_id = inv_id;
1041:
1042: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
1043: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
1044: IS
1045: SELECT accounting_date
1046: ,period_name
1047: ,deferred_acctg_flag
1050: ,def_acctg_number_of_periods
1051: ,def_acctg_period_type
1052: ,set_of_books_id
1053: ,wfapproval_status -- Bug 4863208
1054: FROM ap_invoice_lines_all
1055: WHERE invoice_id = inv_id
1056: AND line_number = cpn_max_line_num;
1057:
1058: -- Added by Jason Liu for retroactive price on 2008/01/09
1065: -----------------------------------------------------------
1066: rec_max_ap_lines_all CUR_GET_MAX_AP_INV_LINE%ROWTYPE;
1067:
1068: ln_max_lnno NUMBER;
1069: ln_inv_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
1070: ln_user_id NUMBER;
1071: ln_login_id NUMBER;
1072: lv_misc VARCHAR2 (15);
1073: lv_match_type VARCHAR2(15);
1255: Fnd_File.put_line(Fnd_File.LOG, 'Start of function apportion_tax_4_price_cor_inv');
1256:
1257: begin
1258: execute immediate 'select /* price_correct_inv_id */, po_distribution_id, amount, quantity_invoiced
1259: from ap_invoice_lines_all
1260: where invoice_id = :inv_id
1261: and line_number = :inv_line_num'
1262: into cur_price_correct_inv_id, cur_po_distribution_id, cur_amount, cur_quantity_invoiced
1263: USING inv_id, pn_invoice_line_number; -- Using pn_invoice_line_number instead of dist_line_no for Bug#4445989
1689: part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
1690: cursor get_ven_info.
1691:
1692: 47 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
1693: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
1694: Resolution:- Code modified to consider ap_invoice_lines_all instead of
1695: ap_invoice_distributions_all.
1696: Following changes done
1697: - Code refering to ap_invoice_distributions_all modified to consider
1690: cursor get_ven_info.
1691:
1692: 47 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
1693: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
1694: Resolution:- Code modified to consider ap_invoice_lines_all instead of
1695: ap_invoice_distributions_all.
1696: Following changes done
1697: - Code refering to ap_invoice_distributions_all modified to consider
1698: invoice_id and invoice_line_number as unique combination
1695: ap_invoice_distributions_all.
1696: Following changes done
1697: - Code refering to ap_invoice_distributions_all modified to consider
1698: invoice_id and invoice_line_number as unique combination
1699: - invoice line record is created in ap_invoice_lines_all where ever previously
1700: distribution was created
1701: - Obsoleted JAI_CMN_FA_INV_DIST_ALL
1702: - Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
1703: and also the fields from JAI_CMN_FA_INV_DIST_ALL
1717: 51. 09-Jan-2008 Modifed by Jason Liu for retroactive price
1718:
1719: 52. 04-Feb-2008 JMEENA for bug#6780154
1720:
1721: Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
1722:
1723:
1724: 53. 21-Feb-2008 JMEENA for bug#6835548
1725: Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
1874: set last_update_date = last_update_date
1875: where invoice_id = inv_id;
1876:
1877: -- Added by Brathod for Bug# 4445989
1878: update ap_invoice_lines_all
1879: set last_update_date = last_update_date
1880: where invoice_id = inv_id;
1881: -- Cursor to select maximum line number from invoice lines for particular invoice
1882: SELECT max(line_number)
1880: where invoice_id = inv_id;
1881: -- Cursor to select maximum line number from invoice lines for particular invoice
1882: SELECT max(line_number)
1883: INTO ln_inv_line_num
1884: FROM ap_invoice_lines_all
1885: WHERE invoice_id = inv_id;
1886: -- End of Bug# 4445989
1887:
1888: /*
2587: --ln_inv_line_num := ln_inv_line_num + 1; commented out by eric for inclusive tax
2588:
2589: -- Added by Brathod for Bug#4445989
2590: Fnd_File.put_line(Fnd_File.LOG,
2591: 'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
2592:
2593: --insert exclusive tax to the ap tables
2594: --or insert recoverable inclusive tax with project information the ap tables
2595:
2607: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
2608: ---------------------------------------------------------------------------
2609: --added by Eric for inclusive tax on 20-dec-2007,end
2610:
2611: INSERT INTO ap_invoice_lines_all
2612: (
2613: INVOICE_ID
2614: , LINE_NUMBER
2615: , LINE_TYPE_LOOKUP_CODE
3439: -- End 5763527
3440: --
3441:
3442: Fnd_File.put_line(Fnd_File.LOG,
3443: 'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
3444:
3445:
3446: --insert exclusive tax to the ap tables
3447: --or insert recoverable inclusive tax with project information the ap tables
3460: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
3461: ---------------------------------------------------------------------------
3462: --added by Eric for inclusive tax on 20-dec-2007,end
3463:
3464: INSERT INTO ap_invoice_lines_all
3465: (
3466: INVOICE_ID
3467: , LINE_NUMBER
3468: , LINE_TYPE_LOOKUP_CODE
4292: --added by Eric for inclusive tax on 20-dec-2007,end
4293:
4294:
4295:
4296: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'
4297: || ln_inv_line_num );
4298:
4299:
4300: --insert exclusive tax to the ap tables
4314: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
4315: ---------------------------------------------------------------------------
4316: --added by Eric for inclusive tax on 20-dec-2007,end
4317:
4318: INSERT INTO ap_invoice_lines_all
4319: (
4320: INVOICE_ID
4321: , LINE_NUMBER
4322: , LINE_TYPE_LOOKUP_CODE
5249: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
5250: ---------------------------------------------------------------------------
5251: --added by Eric for inclusive tax on 20-dec-2007,end
5252:
5253: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
5254:
5255: INSERT INTO ap_invoice_lines_all
5256: (
5257: INVOICE_ID
5251: --added by Eric for inclusive tax on 20-dec-2007,end
5252:
5253: Fnd_File.put_line(Fnd_File.LOG,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
5254:
5255: INSERT INTO ap_invoice_lines_all
5256: (
5257: INVOICE_ID
5258: , LINE_NUMBER
5259: , LINE_TYPE_LOOKUP_CODE
5736: --> Cursor to fetch the maximum line_number for current invoice, Bug# 4445989
5737: CURSOR cur_get_max_line_number
5738: IS
5739: SELECT max (line_number)
5740: FROM ap_invoice_lines_all
5741: WHERE invoice_id = inv_id;
5742:
5743: ln_min_dist_line_num NUMBER;
5744: lv_match_type varchar2(15);
5742:
5743: ln_min_dist_line_num NUMBER;
5744: lv_match_type varchar2(15);
5745: ln_max_lnno NUMBER;
5746: ln_inv_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
5747: ln_user_id NUMBER;
5748: ln_login_id NUMBER;
5749: lv_misc VARCHAR2 (15);
5750: -- Bug 7249100. Added by Lakshmi Gopalsami
5749: lv_misc VARCHAR2 (15);
5750: -- Bug 7249100. Added by Lakshmi Gopalsami
5751: lv_dist_class VARCHAR2(30);
5752:
5753: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
5754: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
5755: IS
5756: SELECT accounting_date
5757: ,period_name
5750: -- Bug 7249100. Added by Lakshmi Gopalsami
5751: lv_dist_class VARCHAR2(30);
5752:
5753: --> Cursor to fetch maximum line from ap_invoice_lines_all for current invoice, Bug# 4445989
5754: CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
5755: IS
5756: SELECT accounting_date
5757: ,period_name
5758: ,deferred_acctg_flag
5761: ,def_acctg_number_of_periods
5762: ,def_acctg_period_type
5763: ,set_of_books_id
5764: , wfapproval_status
5765: FROM ap_invoice_lines_all
5766: WHERE invoice_id = inv_id
5767: AND line_number = cpn_max_line_num;
5768:
5769: rec_max_ap_lines_all CUR_GET_MAX_AP_INV_LINE%ROWTYPE;
6551: part of R12 LE Initiative. The legal_entity_id is selected from ap_invoices_all in
6552: cursor get_ven_info.
6553:
6554: 44 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
6555: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
6556: Resolution:- Code modified to consider ap_invoice_lines_all instead of
6557: ap_invoice_distributions_all.
6558: Following changes done
6559: - Code refering to ap_invoice_distributions_all modified to consider
6552: cursor get_ven_info.
6553:
6554: 44 23-Jun-2005 Brathod for Bug# 4445989, Version 120.0
6555: Issue: Impact uptake on IL Product for AP_INVOICE_LINES_ALL Uptake
6556: Resolution:- Code modified to consider ap_invoice_lines_all instead of
6557: ap_invoice_distributions_all.
6558: Following changes done
6559: - Code refering to ap_invoice_distributions_all modified to consider
6560: invoice_id and invoice_line_number as unique combination
6557: ap_invoice_distributions_all.
6558: Following changes done
6559: - Code refering to ap_invoice_distributions_all modified to consider
6560: invoice_id and invoice_line_number as unique combination
6561: - invoice line record is created in ap_invoice_lines_all where ever previously
6562: distribution was created
6563: - Obsoleted JAI_CMN_FA_INV_DIST_ALL
6564: - Modified structure of JAI_AP_MATCH_INV_TAXES to incorporate invoice lines
6565: and also the fields from JAI_CMN_FA_INV_DIST_ALL
6569: 1) Commented the cursor - for_acct_id and corresponding open/fetch/close for the same.
6570: 2) Commented the call to function - jai_general_pkg.get_accounting_method
6571:
6572: 46. 08-Dec-2005 Bug 4863208. Added by Lakshmi Gopalsami Version 120.2
6573: (1) Added wfapproval_Status in insert to ap_invoice_lines_all
6574: (2) Derived the org_id for inserting into ap_invoice_distributions_all
6575: This is added in CURSOR for_dist_insertion
6576: (3) Added values for match_status_flag, reversal_flag in insert
6577:
6576: (3) Added values for match_status_flag, reversal_flag in insert
6577:
6578: 47. 04-Feb-2008 JMEENA for bug#6780154
6579:
6580: Modify the insert statement of ap_invoice_lines_all to populate the po_distribution_id.
6581:
6582:
6583: 48. 21-Feb-2008 JMEENA for bug#6835548
6584: Changed the value of lv_match_type from NOT MATCHED to NOT_MATCHED.
6713: BEGIN --06-Mar-2002
6714:
6715:
6716: -- Cursor to select maximum line number from invoice lines for particular invoice
6717: -- Lock ap_invoice_lines_all
6718: UPDATE ap_invoice_lines_all
6719: SET last_update_date = last_update_date
6720: WHERE invoice_id = inv_id;
6721:
6714:
6715:
6716: -- Cursor to select maximum line number from invoice lines for particular invoice
6717: -- Lock ap_invoice_lines_all
6718: UPDATE ap_invoice_lines_all
6719: SET last_update_date = last_update_date
6720: WHERE invoice_id = inv_id;
6721:
6722: SELECT max(line_number)
6720: WHERE invoice_id = inv_id;
6721:
6722: SELECT max(line_number)
6723: INTO ln_inv_line_num
6724: FROM ap_invoice_lines_all
6725: WHERE invoice_id = inv_id;
6726: -- End of Bug# 4445989
6727: /*
6728: Commented by Brathod for Bug# 4445989
7264: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
7265: ---------------------------------------------------------------------------
7266: --added by Eric for inclusive tax on 20-dec-2007,end
7267:
7268: INSERT INTO ap_invoice_lines_all
7269: (
7270: INVOICE_ID
7271: , LINE_NUMBER
7272: , LINE_TYPE_LOOKUP_CODE
8112: ---------------------------------------------------------------------------
8113: --added by Eric for inclusive tax on 20-dec-2007,end
8114:
8115:
8116: INSERT INTO ap_invoice_lines_all
8117: (
8118: INVOICE_ID
8119: , LINE_NUMBER
8120: , LINE_TYPE_LOOKUP_CODE
8936: --added by Eric for inclusive tax on 20-dec-2007,end
8937:
8938:
8939: Fnd_File.put_line(Fnd_File.LOG,
8940: 'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
8941:
8942: --insert exclusive tax to the ap tables
8943: --or insert recoverable inclusive tax with project information the ap tables
8944:
8956: ln_inv_line_num := ln_inv_line_num + 1; --added by eric for inlcusive tax
8957: ---------------------------------------------------------------------------
8958: --added by Eric for inclusive tax on 20-dec-2007,end
8959:
8960: INSERT INTO ap_invoice_lines_all
8961: (
8962: INVOICE_ID
8963: , LINE_NUMBER
8964: , LINE_TYPE_LOOKUP_CODE