[Home] [Help]
780: nvl(AI.exclude_freight_from_discount,'N'),
781: nvl(AI.net_of_retainage_flag,'N'), --9356460
782: nvl(pvs.tolerance_id,ASP.tolerance_id), --added nvl for bug 8425996
783: nvl(pvs.services_tolerance_id,ASP.services_tolerance_id) --added nvl for bug 8425996
784: FROM ap_invoices_all AI,
785: ap_suppliers PV,
786: ap_supplier_sites_all PVS,
787: ap_system_parameters_all ASP --added table for bug 8425996
788: WHERE AI.invoice_id = p_invoice_id
809: AI.org_id,
810: nvl(AI.disc_is_inv_less_tax_flag,'N'),
811: nvl(AI.exclude_freight_from_discount,'N'),
812: nvl(AI.net_of_retainage_flag,'N') --9356460
813: FROM ap_invoices_all AI
814: WHERE AI.invoice_id = p_invoice_id;
815:
816: CURSOR invoice_type_cur IS
817: SELECT invoice_type_lookup_code
814: WHERE AI.invoice_id = p_invoice_id;
815:
816: CURSOR invoice_type_cur IS
817: SELECT invoice_type_lookup_code
818: FROM ap_invoices_all
819: WHERE invoice_id = p_invoice_id;
820:
821: l_unfreeze_count NUMBER:= 0; --Bug9021265
822: TAX_UNFREEZE_EXCEPTION EXCEPTION; --Bug9021265
869: l_debug_loc VARCHAR2(30) := 'Approval';
870: l_curr_calling_sequence VARCHAR2(2000);
871: l_debug_info VARCHAR2(2000);
872:
873: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
870: l_curr_calling_sequence VARCHAR2(2000);
871: l_debug_info VARCHAR2(2000);
872:
873: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
871: l_debug_info VARCHAR2(2000);
872:
873: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
872:
873: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
873: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
874: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
882:
875: l_exchange_date AP_INVOICES.exchange_date%TYPE;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
882:
883: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
876: l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
882:
883: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
884:
877: l_invoice_base_amount AP_INVOICES.base_amount%TYPE;
878: l_vendor_id AP_INVOICES.vendor_id%TYPE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
882:
883: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
884:
885: l_insufficient_data_exist BOOLEAN := FALSE;
879: l_invoice_date AP_INVOICES.invoice_date%TYPE;
880: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
881: l_invoice_type AP_INVOICES.invoice_type_lookup_code%TYPE;
882:
883: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
884:
885: l_insufficient_data_exist BOOLEAN := FALSE;
886: l_line_variance_hold_exist BOOLEAN := FALSE;
887: l_need_to_round_flag VARCHAR2(1) := 'Y';
899: Tax_Exception EXCEPTION;
900: Global_Exception EXCEPTION;
901: LCM_Exception EXCEPTION; --Bug 7718385
902: --Retropricing
903: l_invoice_type_lookup_code AP_INVOICES_ALL.invoice_type_lookup_code%TYPE;
904:
905: l_invoice_type_pr AP_INVOICES_ALL.invoice_type_lookup_code%TYPE;
906: l_exclude_tax_from_discount VARCHAR2(1);
907: l_exclude_freight_from_disc VARCHAR2(1);
901: LCM_Exception EXCEPTION; --Bug 7718385
902: --Retropricing
903: l_invoice_type_lookup_code AP_INVOICES_ALL.invoice_type_lookup_code%TYPE;
904:
905: l_invoice_type_pr AP_INVOICES_ALL.invoice_type_lookup_code%TYPE;
906: l_exclude_tax_from_discount VARCHAR2(1);
907: l_exclude_freight_from_disc VARCHAR2(1);
908: l_net_of_retainage_flag VARCHAR2(1); --9356460
909: l_cur_count NUMBER := 0;
930: -- Bug 6648094
931: l_invoice_line_count NUMBER :=0; --Bug 6684139
932: l_encumbrance_exists NUMBER := 0; -- Bug 6681580
933:
934: l_inv_header_rec ap_invoices_all%rowtype;
935: l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
936:
937: -- Project LCM 7588322
938: l_lcm_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1502: ---------------------------------------------------------------
1503:
1504: SELECT invoice_amount, base_amount
1505: INTO l_inv_amount , l_inv_base_amount
1506: FROM ap_invoices
1507: WHERE invoice_id = l_invoice_id;
1508:
1509: ---------------------------------------------------------------
1510: l_debug_info := 'Invoice amount: '|| l_inv_amount ||
1655: --Bug9436217
1656: /*
1657: SELECT invoice_type_lookup_code
1658: INTO l_invoice_type_lookup_code
1659: FROM ap_invoices_all
1660: WHERE invoice_id = l_invoice_id;
1661: */
1662: l_invoice_type_lookup_code := l_invoice_type;
1663: --Bug9436217
1912: SELECT COUNT(1)
1913: INTO l_zx_lines_det_fac_count
1914: FROM zx_lines_det_factors
1915: WHERE application_id = 200
1916: AND entity_code = 'AP_INVOICES'
1917: AND trx_id = l_invoice_id
1918: AND event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
1919: AND ROWNUM = 1 ;
1920:
2298: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2299: END IF;
2300: ----------------------------------------------------------------
2301:
2302: UPDATE ap_invoices_all
2303: SET force_revalidation_flag = 'N'
2304: WHERE invoice_id = l_invoice_id;
2305:
2306: END IF; -- end of approve_invoice_cur cursor count check
2370:
2371: BEGIN
2372: SELECT *
2373: INTO l_inv_header_rec
2374: FROM ap_invoices_all
2375: WHERE invoice_id = P_Invoice_Id;
2376: END;
2377: --BUG 6974733
2378: IF (l_inv_header_rec.invoice_type_lookup_code NOT IN ('AWT', 'INTEREST')) THEN
2432: SELECT COUNT(1)
2433: INTO l_unfreeze_count
2434: FROM zx_rec_nrec_dist
2435: WHERE application_id = 200
2436: AND entity_code = 'AP_INVOICES'
2437: AND event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
2438: AND trx_id = l_inv_header_rec.invoice_id
2439: AND freeze_flag = 'N';
2440: -----------------------------------------------------------------
2628: and NVL(generate_dists,'N') <> 'D';
2629:
2630: CURSOR get_inv_source IS
2631: SELECT upper(nvl(source, 'X')), org_id
2632: FROM ap_invoices_all
2633: WHERE invoice_id = p_invoice_id;
2634:
2635: l_source ap_invoices_all.source%TYPE;
2636: l_org_id ap_invoices_all.org_id%TYPE;
2631: SELECT upper(nvl(source, 'X')), org_id
2632: FROM ap_invoices_all
2633: WHERE invoice_id = p_invoice_id;
2634:
2635: l_source ap_invoices_all.source%TYPE;
2636: l_org_id ap_invoices_all.org_id%TYPE;
2637:
2638: BEGIN
2639: OPEN get_inv_source;
2632: FROM ap_invoices_all
2633: WHERE invoice_id = p_invoice_id;
2634:
2635: l_source ap_invoices_all.source%TYPE;
2636: l_org_id ap_invoices_all.org_id%TYPE;
2637:
2638: BEGIN
2639: OPEN get_inv_source;
2640: FETCH get_inv_source INTO l_source, l_org_id;
3059: |
3060: *============================================================================*/
3061:
3062: PROCEDURE Check_Insufficient_Line_Data(
3063: p_inv_line_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
3064: p_system_user IN NUMBER,
3065: p_holds IN OUT NOCOPY HOLDSARRAY,
3066: p_holds_count IN OUT NOCOPY COUNTARRAY,
3067: p_release_count IN OUT NOCOPY COUNTARRAY,
3223: p_invoice_currency IN VARCHAR2,
3224: p_exchange_rate IN NUMBER,
3225: p_exchange_rate_type IN VARCHAR2,
3226: p_exchange_date IN DATE,
3227: p_inv_line_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
3228: p_system_user IN NUMBER,
3229: p_holds IN OUT NOCOPY HOLDSARRAY,
3230: p_holds_count IN OUT NOCOPY COUNTARRAY,
3231: p_release_count IN OUT NOCOPY COUNTARRAY,
3778: l_curr_calling_sequence VARCHAR2(2000);
3779: l_debug_info VARCHAR2(1000);
3780:
3781: -- 8691645
3782: l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
3783: l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
3784: l_remit_to_supplier_site_id AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE;
3785: l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3786: l_vendor_site_reg_expired varchar2(1);
3779: l_debug_info VARCHAR2(1000);
3780:
3781: -- 8691645
3782: l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
3783: l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
3784: l_remit_to_supplier_site_id AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE;
3785: l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3786: l_vendor_site_reg_expired varchar2(1);
3787:
3780:
3781: -- 8691645
3782: l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
3783: l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
3784: l_remit_to_supplier_site_id AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE;
3785: l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3786: l_vendor_site_reg_expired varchar2(1);
3787:
3788: BEGIN
3781: -- 8691645
3782: l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
3783: l_vendor_site_id AP_INVOICES.VENDOR_SITE_ID%TYPE;
3784: l_remit_to_supplier_site_id AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE;
3785: l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
3786: l_vendor_site_reg_expired varchar2(1);
3787:
3788: BEGIN
3789:
3887: Select vendor_id,vendor_site_id,
3888: remit_to_supplier_site_id,invoice_type_lookup_code
3889: into l_vendor_id,l_vendor_site_id,
3890: l_remit_to_supplier_site_id,l_invoice_type_lookup_code
3891: from ap_invoices_all
3892: where invoice_id = p_invoice_id;
3893:
3894: if ( l_invoice_type_lookup_code = 'RETAINAGE RELEASE') then
3895:
3960: /* Select vendor_id,vendor_site_id,
3961: remit_to_supplier_site_id,invoice_type_lookup_code
3962: into l_vendor_id,l_vendor_site_id,
3963: l_remit_to_supplier_site_id,l_invoice_type_lookup_code
3964: from ap_invoices_all
3965: where invoice_id = p_invoice_id; */
3966:
3967:
3968:
4215: p_calling_sequence IN VARCHAR2) IS
4216:
4217: CURSOR PO_Required_Cur IS
4218: -- Perf bug 5058995
4219: -- Modify below SQL to go to base tables : AP_INVOICES_ALL,
4220: -- AP_INVOICE_DISTRIBUTIONS_ALL and
4221: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4220: -- AP_INVOICE_DISTRIBUTIONS_ALL and
4221: -- AP_SUPPLIER_SITES(instead of po_vendor_sites)
4222: /* Added the Hint index(apd AP_INVOICE_DISTRIBUTIONS_U1) for bug#7270053 */
4223: SELECT 'PO REQUIRED'
4224: FROM ap_invoices_all api, ap_supplier_sites pov
4225: WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
4226: from ap_invoice_distributions_all apd
4227: where apd.invoice_id = api.invoice_id
4228: and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
4330: p_calling_sequence IN VARCHAR2) IS
4331:
4332: CURSOR No_Rate_Cur IS
4333: SELECT 'Foreign Invoice without exchange rate'
4334: FROM ap_invoices I
4335: WHERE I.invoice_id = p_invoice_id
4336: AND I.invoice_currency_code <> p_base_currency_code
4337: AND I.exchange_rate is null;
4338:
4578: l_debug_loc VARCHAR2(30) := 'Check_Dist_Variance';
4579: l_curr_calling_sequence VARCHAR2(2000);
4580: l_debug_info VARCHAR2(1000);
4581: l_test_var VARCHAR2(50);
4582: l_inv_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
4583: l_dist_count NUMBER;
4584:
4585: BEGIN
4586:
4594:
4595: -- Bug 4539514
4596: SELECT invoice_amount
4597: INTO l_inv_amount
4598: FROM ap_invoices_all ai
4599: WHERE ai.invoice_id = p_invoice_id;
4600:
4601: SELECT count(*) INTO l_dist_count
4602: FROM ap_invoice_distributions_all aid
4690: -- PROCEDURE CHECK_CCR_VENDOR
4691: ============================================================================*/
4692:
4693: PROCEDURE CHECK_CCR_VENDOR(
4694: P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
4695: P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
4696: P_VENDOR_SITE_ID IN AP_INVOICES.VENDOR_SITE_ID%TYPE,
4697: P_REMIT_TO_SUPPLIER_SITE_ID IN AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
4698: P_SYSTEM_USER IN NUMBER,
4691: ============================================================================*/
4692:
4693: PROCEDURE CHECK_CCR_VENDOR(
4694: P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
4695: P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
4696: P_VENDOR_SITE_ID IN AP_INVOICES.VENDOR_SITE_ID%TYPE,
4697: P_REMIT_TO_SUPPLIER_SITE_ID IN AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
4698: P_SYSTEM_USER IN NUMBER,
4699: P_HOLDS IN OUT NOCOPY HOLDSARRAY,
4692:
4693: PROCEDURE CHECK_CCR_VENDOR(
4694: P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
4695: P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
4696: P_VENDOR_SITE_ID IN AP_INVOICES.VENDOR_SITE_ID%TYPE,
4697: P_REMIT_TO_SUPPLIER_SITE_ID IN AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
4698: P_SYSTEM_USER IN NUMBER,
4699: P_HOLDS IN OUT NOCOPY HOLDSARRAY,
4700: P_HOLDS_COUNT IN OUT NOCOPY COUNTARRAY,
4693: PROCEDURE CHECK_CCR_VENDOR(
4694: P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
4695: P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
4696: P_VENDOR_SITE_ID IN AP_INVOICES.VENDOR_SITE_ID%TYPE,
4697: P_REMIT_TO_SUPPLIER_SITE_ID IN AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
4698: P_SYSTEM_USER IN NUMBER,
4699: P_HOLDS IN OUT NOCOPY HOLDSARRAY,
4700: P_HOLDS_COUNT IN OUT NOCOPY COUNTARRAY,
4701: P_RELEASE_COUNT IN OUT NOCOPY COUNTARRAY,
4860:
4861:
4862: CURSOR Line_Var_Cur IS
4863: SELECT 'Line Total <> Invoice Amount'
4864: FROM ap_invoice_lines_all AIL, ap_invoices_all A
4865: WHERE AIL.invoice_id = A.invoice_id
4866: AND AIL.invoice_id = p_invoice_id
4867: AND ((AIL.line_type_lookup_code <> 'TAX'
4868: and (AIL.line_type_lookup_code NOT IN ('AWT','PREPAY')
4882: l_test_var VARCHAR2(50);
4883: l_debug_loc VARCHAR2(30) := 'Check_Line_Variance';
4884: l_curr_calling_sequence VARCHAR2(2000);
4885: l_debug_info VARCHAR2(1000);
4886: l_inv_cur_code ap_invoices.invoice_currency_code%type;
4887: l_inv_amount ap_invoices_all.invoice_amount%TYPE;
4888: l_line_count number;
4889: l_org_id ap_invoices_all.org_id%TYPE; --bug 7271262
4890: l_set_of_books_id ap_invoices_all.set_of_books_id%TYPE; --bug 7271262
4883: l_debug_loc VARCHAR2(30) := 'Check_Line_Variance';
4884: l_curr_calling_sequence VARCHAR2(2000);
4885: l_debug_info VARCHAR2(1000);
4886: l_inv_cur_code ap_invoices.invoice_currency_code%type;
4887: l_inv_amount ap_invoices_all.invoice_amount%TYPE;
4888: l_line_count number;
4889: l_org_id ap_invoices_all.org_id%TYPE; --bug 7271262
4890: l_set_of_books_id ap_invoices_all.set_of_books_id%TYPE; --bug 7271262
4891: l_return_code VARCHAR2(100); --bug 7271262
4885: l_debug_info VARCHAR2(1000);
4886: l_inv_cur_code ap_invoices.invoice_currency_code%type;
4887: l_inv_amount ap_invoices_all.invoice_amount%TYPE;
4888: l_line_count number;
4889: l_org_id ap_invoices_all.org_id%TYPE; --bug 7271262
4890: l_set_of_books_id ap_invoices_all.set_of_books_id%TYPE; --bug 7271262
4891: l_return_code VARCHAR2(100); --bug 7271262
4892: l_return_message VARCHAR2(1000); --bug 7271262
4893:
4886: l_inv_cur_code ap_invoices.invoice_currency_code%type;
4887: l_inv_amount ap_invoices_all.invoice_amount%TYPE;
4888: l_line_count number;
4889: l_org_id ap_invoices_all.org_id%TYPE; --bug 7271262
4890: l_set_of_books_id ap_invoices_all.set_of_books_id%TYPE; --bug 7271262
4891: l_return_code VARCHAR2(100); --bug 7271262
4892: l_return_message VARCHAR2(1000); --bug 7271262
4893:
4894: BEGIN
4908: --select, to pass as parameters for JAI hook jai_ap_tolerance_pkg.inv_holds_check
4909:
4910: SELECT invoice_amount,org_id,set_of_books_id
4911: INTO l_inv_amount,l_org_id,l_set_of_books_id
4912: FROM ap_invoices ai
4913: WHERE ai.invoice_id = p_invoice_id;
4914:
4915: --Added the below code hook for India Localization as part of bug7271262
4916:
5041: l_debug_context VARCHAR2(2000);
5042:
5043: l_modified_line_rounding_amt NUMBER; --6892789
5044: l_base_amt NUMBER; --6892789
5045: l_round_inv_line_numbers AP_INVOICES_UTILITY_PKG.inv_line_num_tab_type; --6892789
5046: l_amt NUMBER; -- Bug 12686348
5047: l_invoice_type AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE; --Bug#10416960
5048:
5049: BEGIN
5043: l_modified_line_rounding_amt NUMBER; --6892789
5044: l_base_amt NUMBER; --6892789
5045: l_round_inv_line_numbers AP_INVOICES_UTILITY_PKG.inv_line_num_tab_type; --6892789
5046: l_amt NUMBER; -- Bug 12686348
5047: l_invoice_type AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE; --Bug#10416960
5048:
5049: BEGIN
5050:
5051: l_curr_calling_sequence := 'AP_APPROVAL_PKG.'||l_debug_loc||
5122:
5123: --Bug#10416960
5124: SELECT INVOICE_TYPE_LOOKUP_CODE
5125: INTO l_invoice_type
5126: FROM AP_INVOICES_ALL
5127: WHERE INVOICE_ID=p_invoice_id;
5128:
5129: IF(l_invoice_type <> 'PO PRICE ADJUST')THEN
5130: -- get the lines which can be adjusted
5127: WHERE INVOICE_ID=p_invoice_id;
5128:
5129: IF(l_invoice_type <> 'PO PRICE ADJUST')THEN
5130: -- get the lines which can be adjusted
5131: l_round_amt_exist := AP_INVOICES_UTILITY_PKG.round_base_amts(
5132: X_Invoice_Id => p_invoice_id,
5133: X_Reporting_Ledger_Id => NULL,
5134: X_Rounded_Line_Numbers => l_round_inv_line_numbers,
5135: X_Rounded_Amt => l_rounded_amt,
5516: WHERE ail1.invoice_id=ai.invoice_id
5517: AND ail1.line_type_lookup_code ='AWT')
5518: ,nvl(AI.amount_paid,0)
5519: , (0 - sum(nvl(AIL.amount,0))) -- taking the remaining amount to be paid on as part of bug 8339454
5520: FROM ap_invoices_all AI, ap_invoice_lines_all AIL
5521: WHERE AI.invoice_id = p_invoice_id
5522: AND AIL.invoice_id = AI.invoice_id
5523: AND AIL.invoice_includes_prepay_flag = 'N' --Bug 13050996: Uncommented out this line
5524: AND AIL.line_type_lookup_code IN ('PREPAY', 'TAX')
6004: -- invoice has no user non-releaseable holds (ther than AWT ERROR) --
6005: -- invoice has no manual withholding lines --
6006: ------------------------------------------------------------------------
6007: -- Perf bug 5058995
6008: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
6009: -- AP_INVOICE_DISTRIBUTIONS_ALL
6010: SELECT 'OK to call Withholding Routine',
6011: (AI.invoice_amount * NVL(AI.exchange_rate, 1)),
6012: AI.invoice_num
6012: AI.invoice_num
6013: INTO l_ok_to_withhold,
6014: l_withholding_amount,
6015: l_invoice_num
6016: FROM ap_invoices_all AI
6017: WHERE AI.invoice_id = p_invoice_id
6018: AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
6019: FROM ap_invoice_distributions_all AID1
6020: WHERE AID1.invoice_id = AI.invoice_id
6042: --bug 9293773
6043: SELECT nvl(asp.withholding_date_basis,'INVOICEDATE')
6044: INTO l_withhold_date_basis
6045: FROM ap_system_parameters_all asp,
6046: ap_invoices_all ai
6047: WHERE ai.invoice_id = p_invoice_id
6048: AND ai.org_id = asp.org_id;
6049:
6050: IF l_withhold_date_basis = 'INVOICEDATE' THEN
6049:
6050: IF l_withhold_date_basis = 'INVOICEDATE' THEN
6051: SELECT invoice_date
6052: INTO l_withholding_date
6053: FROM ap_invoices
6054: WHERE invoice_id = p_invoice_id;
6055: ELSE
6056: SELECT gl_date
6057: INTO l_withholding_date
6054: WHERE invoice_id = p_invoice_id;
6055: ELSE
6056: SELECT gl_date
6057: INTO l_withholding_date
6058: FROM ap_invoices
6059: WHERE invoice_id = p_invoice_id;
6060: END IF;
6061: --bug 9293773
6062:
6098: -- invoice has no manual withholding lines --
6099: ------------------------------------------------------------------------
6100:
6101: -- Perf bug 5058995
6102: -- Modify below SQL to go to base tables : AP_INVOICES_ALL and
6103: -- AP_INVOICE_DISTRIBUTIONS_ALL
6104: SELECT 'OK to call Withholding Routine',
6105: (AI.invoice_amount * NVL(AI.exchange_rate,1)),
6106: AI.invoice_num
6106: AI.invoice_num
6107: INTO l_ok_to_withhold,
6108: l_withholding_amount,
6109: l_invoice_num
6110: FROM ap_invoices_all AI
6111: WHERE AI.invoice_id = p_invoice_id
6112: AND NOT EXISTS (SELECT 'Unreleased System holds exist'
6113: FROM ap_holds AH,
6114: ap_hold_codes AHC
6690: | This procedure updates/ recreates payment schedules whenever
6691: | 'Expired Registration' hold releases for an invoice.
6692: ============================================================================*/
6693:
6694: PROCEDURE UPDATE_SCHEDULES(P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
6695: P_CALLING_SEQUENCE IN VARCHAR2)IS
6696:
6697: cursor invoice_cursor is
6698: select AI.terms_id,
6706: AI.amount_applicable_to_discount,
6707: AI.payment_method_code,
6708: AI.invoice_currency_code,
6709: AI.payment_currency_code
6710: from ap_invoices AI
6711: where AI.invoice_id = p_invoice_id;
6712:
6713:
6714: l_terms_id ap_invoices.terms_id%type;
6710: from ap_invoices AI
6711: where AI.invoice_id = p_invoice_id;
6712:
6713:
6714: l_terms_id ap_invoices.terms_id%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6711: where AI.invoice_id = p_invoice_id;
6712:
6713:
6714: l_terms_id ap_invoices.terms_id%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6712:
6713:
6714: l_terms_id ap_invoices.terms_id%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6713:
6714: l_terms_id ap_invoices.terms_id%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6714: l_terms_id ap_invoices.terms_id%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6715: l_last_updated_by ap_invoices.last_updated_by%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6716: l_created_by ap_invoices.created_by%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6717: l_batch_id ap_invoices.batch_id%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6725: l_payment_currency_code ap_invoices.payment_currency_code%type;
6718: l_terms_date ap_invoices.terms_date%type;
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6725: l_payment_currency_code ap_invoices.payment_currency_code%type;
6726:
6719: l_invoice_amount ap_invoices.invoice_amount%type;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6725: l_payment_currency_code ap_invoices.payment_currency_code%type;
6726:
6727: l_paid_schd_count NUMBER;
6720: l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%type;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6725: l_payment_currency_code ap_invoices.payment_currency_code%type;
6726:
6727: l_paid_schd_count NUMBER;
6728: l_old_terms_date Date;
6721: l_payment_cross_rate ap_invoices.payment_cross_rate%type;
6722: l_amt_applicable_to_discount ap_invoices.amount_applicable_to_discount%type;
6723: l_payment_method_code ap_invoices.payment_method_code%type;
6724: l_invoice_currency_code ap_invoices.invoice_currency_code%type;
6725: l_payment_currency_code ap_invoices.payment_currency_code%type;
6726:
6727: l_paid_schd_count NUMBER;
6728: l_old_terms_date Date;
6729: l_current_calling_sequence VARCHAR2(2000);
6738: END IF;
6739:
6740: select terms_date
6741: into l_old_terms_date
6742: from ap_invoices_all
6743: where invoice_id = p_invoice_id;
6744:
6745: l_debug_info := 'Before updating invoice terms_date';
6746: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6746: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6747: fnd_file.put_line(FND_FILE.LOG,l_debug_info);
6748: END IF;
6749:
6750: Update ap_invoices_all
6751: set terms_date =trunc(sysdate),
6752: invoice_received_date = decode(invoice_received_date,null,null,trunc(sysdate)) --bug9148859
6753: where invoice_id = p_invoice_id;
6754:
7087:
7088: --Introduced select stmt for 8691645
7089:
7090: select org_id into l_org_id
7091: from ap_invoices where invoice_id = p_invoice_id;
7092:
7093: INSERT INTO ap_holds (
7094: invoice_id,
7095: line_location_id,
7524: p_approval_status IN OUT NOCOPY VARCHAR2,
7525: p_calling_sequence IN VARCHAR2) IS
7526:
7527: CURSOR Invoice_Status_Cur IS
7528: SELECT AP_INVOICES_PKG.Get_Holds_Count(invoice_id),
7529: AP_INVOICES_PKG.Get_Approval_Status(
7530: invoice_id,
7531: invoice_amount,
7532: payment_status_flag,
7525: p_calling_sequence IN VARCHAR2) IS
7526:
7527: CURSOR Invoice_Status_Cur IS
7528: SELECT AP_INVOICES_PKG.Get_Holds_Count(invoice_id),
7529: AP_INVOICES_PKG.Get_Approval_Status(
7530: invoice_id,
7531: invoice_amount,
7532: payment_status_flag,
7533: invoice_type_lookup_code)
7530: invoice_id,
7531: invoice_amount,
7532: payment_status_flag,
7533: invoice_type_lookup_code)
7534: FROM ap_invoices
7535: WHERE invoice_id = p_invoice_id;
7536:
7537: l_debug_loc VARCHAR2(30) := 'Get_Invoice_Statuses';
7538: l_curr_calling_sequence VARCHAR2(2000);
7834: l_invoice_date,
7835: l_org_id --8405589
7836: --Commented for bug#9356460
7837: -- l_net_of_retainage_flag --8405589
7838: FROM ap_invoices
7839: WHERE invoice_id = p_invoice_id;
7840:
7841: EXCEPTION
7842: WHEN OTHERS THEN
7861: --Introduced abs() for bug#9356460
7862: --8405589 Starts
7863:
7864: if nvl(p_net_of_retainage_flag, 'N') <> 'Y' then
7865: l_retained_amount := abs(nvl(ap_invoices_utility_pkg.get_retained_total
7866: (p_invoice_id, l_org_id),0));
7867: end if;
7868: --8405589 Ends
7869:
7988: l_debug_info := 'Update Invoice Header';
7989: ------------------------------------------------------------
7990:
7991: --Introduced l_retained_amount for bug#9356460
7992: UPDATE ap_invoices
7993: SET amount_applicable_to_discount = l_new_discountable_amount,
7994: validated_tax_amount = l_total_tax_amount+l_total_freight_amount+l_retained_amount
7995: WHERE invoice_id = p_invoice_id;
7996:
8023: /* bug 13035539 starts */
8024:
8025: select BASE_CURRENCY_CODE
8026: into l_tmp_base_curr_code
8027: from ap_system_parameters_all asp, ap_invoices_all ai
8028: where asp.org_id = ai.org_id
8029: and ai.invoice_id = p_invoice_id;
8030:
8031: /* bug 13035539 ends */
8029: and ai.invoice_id = p_invoice_id;
8030:
8031: /* bug 13035539 ends */
8032:
8033: UPDATE ap_invoices
8034: SET invoice_amount = l_tmp_invoice_amount,
8035: amount_applicable_to_discount = l_tmp_invoice_amount,
8036: /* bug 13035539 starts */
8037: base_amount =
8085: l_payment_cross_rate,
8086: l_payment_method,
8087: l_invoice_curr_code,
8088: l_pay_curr_code
8089: FROM ap_invoices
8090: WHERE invoice_id = p_invoice_id;
8091:
8092: EXCEPTION
8093: WHEN OTHERS THEN
8147: --where prepayment is applied. This will be called from the prcedure
8148: -- Exclude_Tax_Freight_From_Discount for the feature as described in the description of
8149: -- the former procedure. A similar procedure exists in prepayment package
8150: -- however due to some restriction we are creating the procedure here. This
8151: -- procedure will not update ap_invoices, but will update payment schedule.
8152: -------------------------------------------------------------------------------
8153:
8154: PROCEDURE update_payment_schedule_prepay(
8155: p_invoice_id IN NUMBER,
8304: CLOSE SCHEDULES;
8305:
8306: --Bug 4539462 DBI logging
8307: AP_DBI_PKG.Maintain_DBI_Summary
8308: (p_table_name => 'AP_INVOICES',
8309: p_operation => 'U',
8310: p_key_value1 => P_invoice_id,
8311: p_calling_sequence => l_current_calling_sequence);
8312:
8345: p_last_updated_by IN NUMBER,
8346: p_last_update_login IN NUMBER,
8347: p_calling_sequence IN VARCHAR2) IS
8348: l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8349: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8350: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8351: l_num_payments NUMBER := 0;
8352: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;--1724924
8353: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8346: p_last_update_login IN NUMBER,
8347: p_calling_sequence IN VARCHAR2) IS
8348: l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8349: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8350: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8351: l_num_payments NUMBER := 0;
8352: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;--1724924
8353: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8354: l_gross_amount ap_payment_schedules.gross_amount%TYPE := 0;
8348: l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8349: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8350: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8351: l_num_payments NUMBER := 0;
8352: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;--1724924
8353: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8354: l_gross_amount ap_payment_schedules.gross_amount%TYPE := 0;
8355: l_debug_loc VARCHAR2(30) := 'Manual_Withhold_Tax';
8356: l_curr_calling_sequence VARCHAR2(2000);
8386: invoice_type_lookup_code --Bug 1724924
8387: INTO l_payment_cross_rate,
8388: l_pay_curr_code,
8389: l_invoice_type --Bug 1724924
8390: FROM ap_invoices
8391: WHERE invoice_id = p_invoice_id;
8392:
8393: --===================================================================
8394: --Prorate the manual AWT against the invoice amount remaining
8429: payment_status_flag = DECODE(payment_status_flag,
8430: 'Y','P',payment_status_flag)
8431: where invoice_id = p_invoice_id;
8432:
8433: update ap_invoices
8434: set payment_status_flag = DECODE(payment_status_flag,
8435: 'Y','P',payment_status_flag)
8436: where invoice_id = p_invoice_id;
8437:
8467: SELECT payment_num,gross_amount,amount_remaining
8468: FROM ap_payment_schedules
8469: WHERE invoice_id=p_invoice_id;
8470: l_automatic_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8471: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8472: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8473: l_num_payments NUMBER := 0;
8474: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
8475: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8468: FROM ap_payment_schedules
8469: WHERE invoice_id=p_invoice_id;
8470: l_automatic_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8471: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8472: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8473: l_num_payments NUMBER := 0;
8474: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
8475: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8476: l_gross_amount ap_payment_schedules.gross_amount%TYPE := 0;
8470: l_automatic_awt_amount ap_invoice_distributions.amount%TYPE :=0;
8471: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8472: l_pay_curr_code ap_invoices.payment_currency_code%TYPE;
8473: l_num_payments NUMBER := 0;
8474: l_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
8475: l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
8476: l_gross_amount ap_payment_schedules.gross_amount%TYPE := 0;
8477: l_payment_num ap_payment_schedules.payment_num%TYPE;
8478: l_debug_loc VARCHAR2(30) := 'Update_Pay_Sched_For_Awt';
8498: invoice_type_lookup_code
8499: INTO l_payment_cross_rate,
8500: l_pay_curr_code,
8501: l_invoice_type
8502: FROM ap_invoices
8503: WHERE invoice_id = p_invoice_id;
8504:
8505:
8506: OPEN Update_payment_schedule;
8513: ai.payment_cross_rate,l_pay_curr_code),0)*
8514: l_gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
8515: nvl(ai.pay_curr_invoice_amount, 1))
8516: into l_wt_amt_to_subtract
8517: from ap_invoices ai
8518: where ai.invoice_id=p_invoice_id;
8519:
8520: --===================================================================
8521: --Prorate the automatic AWT against the invoice amount remaining
8581: l_terms_date DATE;
8582: l_invoice_amount NUMBER;
8583: l_pay_curr_invoice_amount NUMBER;
8584: l_payment_method VARCHAR2(30);
8585: l_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
8586: l_invoice_curr_code VARCHAR2(15);
8587: l_pay_curr_code VARCHAR2(15);
8588: l_debug_info VARCHAR2(200);
8589: l_debug_loc VARCHAR2(30) := 'createPaymentSchedules';
8649: l_payment_cross_rate,
8650: l_payment_method,
8651: l_invoice_curr_code,
8652: l_pay_curr_code
8653: FROM ap_invoices
8654: WHERE invoice_id = p_invoice_id;
8655:
8656: EXCEPTION
8657: WHEN OTHERS THEN
8750:
8751:
8752: FUNCTION Gen_Dists_From_Registration(
8753: P_Batch_Id IN NUMBER,
8754: P_Invoice_Line_Rec IN AP_INVOICES_PKG.r_invoice_line_rec,
8755: P_Registration_Api IN VARCHAR2,
8756: P_Registration_View IN VARCHAR2,
8757: P_Generate_Permanent IN VARCHAR2,
8758: X_Error_Code OUT NOCOPY VARCHAR2,
9175:
9176: IS
9177:
9178: TYPE hold_codeTab IS TABLE OF ap_hold_codes.hold_lookup_code%Type;
9179: TYPE invoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type INDEX BY BINARY_INTEGER;
9180: TYPE invoiceNUMTab IS TABLE OF ap_invoices.invoice_num%Type INDEX BY BINARY_INTEGER;
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9176: IS
9177:
9178: TYPE hold_codeTab IS TABLE OF ap_hold_codes.hold_lookup_code%Type;
9179: TYPE invoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type INDEX BY BINARY_INTEGER;
9180: TYPE invoiceNUMTab IS TABLE OF ap_invoices.invoice_num%Type INDEX BY BINARY_INTEGER;
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9184: TYPE orgIDTab IS TABLE OF ap_invoices_all.org_id%Type INDEX BY BINARY_INTEGER;
9177:
9178: TYPE hold_codeTab IS TABLE OF ap_hold_codes.hold_lookup_code%Type;
9179: TYPE invoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type INDEX BY BINARY_INTEGER;
9180: TYPE invoiceNUMTab IS TABLE OF ap_invoices.invoice_num%Type INDEX BY BINARY_INTEGER;
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9184: TYPE orgIDTab IS TABLE OF ap_invoices_all.org_id%Type INDEX BY BINARY_INTEGER;
9185: TYPE invtypeTab IS TABLE OF ap_invoices_all.invoice_type_lookup_code%Type INDEX BY BINARY_INTEGER;
9178: TYPE hold_codeTab IS TABLE OF ap_hold_codes.hold_lookup_code%Type;
9179: TYPE invoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type INDEX BY BINARY_INTEGER;
9180: TYPE invoiceNUMTab IS TABLE OF ap_invoices.invoice_num%Type INDEX BY BINARY_INTEGER;
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9184: TYPE orgIDTab IS TABLE OF ap_invoices_all.org_id%Type INDEX BY BINARY_INTEGER;
9185: TYPE invtypeTab IS TABLE OF ap_invoices_all.invoice_type_lookup_code%Type INDEX BY BINARY_INTEGER;
9186:
9180: TYPE invoiceNUMTab IS TABLE OF ap_invoices.invoice_num%Type INDEX BY BINARY_INTEGER;
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9184: TYPE orgIDTab IS TABLE OF ap_invoices_all.org_id%Type INDEX BY BINARY_INTEGER;
9185: TYPE invtypeTab IS TABLE OF ap_invoices_all.invoice_type_lookup_code%Type INDEX BY BINARY_INTEGER;
9186:
9187: l_inv_batch_id NUMBER(15);
9188: l_vendor_id NUMBER(15);
9181: TYPE procinvoiceIDTab IS TABLE OF ap_invoices.invoice_id%Type;
9182: TYPE hold_org_idTab IS TABLE OF ap_invoices.org_id%Type;
9183:
9184: TYPE orgIDTab IS TABLE OF ap_invoices_all.org_id%Type INDEX BY BINARY_INTEGER;
9185: TYPE invtypeTab IS TABLE OF ap_invoices_all.invoice_type_lookup_code%Type INDEX BY BINARY_INTEGER;
9186:
9187: l_inv_batch_id NUMBER(15);
9188: l_vendor_id NUMBER(15);
9189: l_pay_group VARCHAR2(25);
9242:
9243: l_retained_amount NUMBER;
9244: l_recouped_amount NUMBER;
9245: l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
9246: l_invoice_date AP_INVOICES.invoice_date%TYPE;
9247: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
9248: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
9249: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
9250: l_exchange_date AP_INVOICES.exchange_date%TYPE;
9243: l_retained_amount NUMBER;
9244: l_recouped_amount NUMBER;
9245: l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
9246: l_invoice_date AP_INVOICES.invoice_date%TYPE;
9247: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
9248: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
9249: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
9250: l_exchange_date AP_INVOICES.exchange_date%TYPE;
9251: l_tolerance_id AP_SUPPLIER_SITES_ALL.TOLERANCE_ID%TYPE;
9244: l_recouped_amount NUMBER;
9245: l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
9246: l_invoice_date AP_INVOICES.invoice_date%TYPE;
9247: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
9248: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
9249: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
9250: l_exchange_date AP_INVOICES.exchange_date%TYPE;
9251: l_tolerance_id AP_SUPPLIER_SITES_ALL.TOLERANCE_ID%TYPE;
9252: l_services_tolerance_id AP_SUPPLIER_SITES_ALL.SERVICES_TOLERANCE_ID%TYPE;
9245: l_invoice_rec AP_APPROVAL_PKG.Invoice_Rec;
9246: l_invoice_date AP_INVOICES.invoice_date%TYPE;
9247: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
9248: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
9249: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
9250: l_exchange_date AP_INVOICES.exchange_date%TYPE;
9251: l_tolerance_id AP_SUPPLIER_SITES_ALL.TOLERANCE_ID%TYPE;
9252: l_services_tolerance_id AP_SUPPLIER_SITES_ALL.SERVICES_TOLERANCE_ID%TYPE;
9253:
9246: l_invoice_date AP_INVOICES.invoice_date%TYPE;
9247: l_invoice_currency_code AP_INVOICES.invoice_currency_code%TYPE;
9248: l_exchange_rate AP_INVOICES.exchange_rate%TYPE;
9249: l_exchange_rate_type AP_INVOICES.exchange_rate_type%TYPE;
9250: l_exchange_date AP_INVOICES.exchange_date%TYPE;
9251: l_tolerance_id AP_SUPPLIER_SITES_ALL.TOLERANCE_ID%TYPE;
9252: l_services_tolerance_id AP_SUPPLIER_SITES_ALL.SERVICES_TOLERANCE_ID%TYPE;
9253:
9254: Tax_Exception EXCEPTION;
9272: nvl(I.disc_is_inv_less_tax_flag,'N') disc_is_inv_less_tax_flag,
9273: nvl(I.exclude_freight_from_discount,'N') exclude_freight_from_discount,
9274: nvl(S.tolerance_id,ASP.tolerance_id), --Bug8524767
9275: nvl(S.services_tolerance_id,ASP.services_tolerance_id) --Bug8524767
9276: FROM ap_invoices_all I,
9277: ap_supplier_sites_all S,
9278: ap_system_parameters_all ASP --Bug8524767
9279: WHERE I.vendor_site_id = S.vendor_site_id (+)
9280: AND I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
9291: -- Start for bug 6511249
9292: --bug7902867 cusror modified removed ap_supplier_sites_all in join
9293: CURSOR SELC_INV_CURSOR_BULK_ERROR IS
9294: SELECT I.invoice_id, i.invoice_num, i.org_id
9295: FROM ap_invoices_all I
9296: WHERE I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
9297: ORDER BY I.org_id;
9298:
9299: TYPE selc_inv_cursor_blk_err is table of selc_inv_cursor_bulk_error%rowtype;
9340: -- bug 9304530 - start
9341: l_invoice_approval_status VARCHAR2(25);
9342:
9343: CURSOR Invoice_Status_Cursor(p_invoice_id NUMBER) IS
9344: SELECT AP_INVOICES_PKG.Get_Approval_Status(
9345: invoice_id,
9346: invoice_amount,
9347: payment_status_flag,
9348: invoice_type_lookup_code)
9345: invoice_id,
9346: invoice_amount,
9347: payment_status_flag,
9348: invoice_type_lookup_code)
9349: FROM ap_invoices_all
9350: WHERE invoice_id = p_invoice_id
9351: FOR UPDATE NOWAIT; -- Bug 13374062
9352: -- bug 9304530 - end
9353:
9399: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Clean-up validation_request_id');
9400: END IF;
9401: ---------------------------------------------------------------------
9402: /* Bug 9777752 : Sl No 4 Change
9403: UPDATE ap_invoices api
9404: SET validation_request_id = NULL
9405: WHERE validation_request_id IS NOT NULL
9406: AND EXISTS
9407: ( SELECT 'Request Completed'
9408: FROM fnd_concurrent_requests fcr
9409: WHERE fcr.request_id = api.validation_request_id
9410: AND fcr.phase_code = 'C' ); */
9411:
9412: UPDATE ap_invoices api
9413: SET validation_request_id = NULL
9414: WHERE validation_request_id IN
9415: ( SELECT request_id
9416: FROM fnd_concurrent_requests fcr
9485:
9486: /* 6699825/6684139: Added the AND condition to check if the invoice has any
9487: lines. Otherwise, we will not proceed with validating the invoice */
9488:
9489: UPDATE ap_invoices_all ai
9490: SET ai.validation_request_id = p_conc_request_id
9491: WHERE ai.invoice_id = l_invoice_id
9492: AND ai.validation_request_id IS NULL
9493: /*bug 7029877 Invoice saved but not submitted*/
9517: l_invoice_num, l_sel_org_id, l_sel_invoice_type, l_validation_request_id,
9518: l_invoice_id, l_invoice_date, l_invoice_currency_code, l_exchange_rate,
9519: l_exchange_rate_type, l_exchange_date, l_vendor_id, l_org_id, l_tolerance_id,
9520: l_services_tolerance_id
9521: FROM ap_invoices_all ai,
9522: ap_supplier_sites_all s,
9523: ap_system_parameters_all asp
9524: WHERE ai.invoice_id = l_invoice_id
9525: AND ai.vendor_site_id = s.vendor_site_id(+)
9699: fnd_message.set_token('INV_NUM', l_invoice_num);
9700: fnd_file.put_line(fnd_file.log, fnd_message.get);
9701: END IF; */
9702:
9703: UPDATE ap_invoices_all
9704: SET validation_request_id = NULL
9705: WHERE invoice_id = l_invoice_id;
9706:
9707: END IF;
9768: /*bug6858309 modified this dynamic update to filter out
9769: recurring invoices havign GL DATE in never open period*/
9770: /* Added for bug#7270053 Start */
9771: --BUG7902867 replace view with base tables in sub queries
9772: -- AP_INVOICES_ALL is replace with ap_invoices
9773: /* BUG 8218038 added nvl condition for historical and payment status flags */
9774: /* Changed the Sql structure to replace the exists with UNION ALL for bug#7584153 */
9775:
9776: -- Bug 9777752 : Restructured dynamic SQL
9774: /* Changed the Sql structure to replace the exists with UNION ALL for bug#7584153 */
9775:
9776: -- Bug 9777752 : Restructured dynamic SQL
9777: l_sql_stmt :=l_sql_stmt||
9778: 'SELECT /*+ dynamic_sampling(2) cardinality(ai,10) */ invoice_id from AP_INVOICES AI -- 7461423
9779: WHERE AI.VALIDATION_REQUEST_ID IS NULL
9780: AND AI.APPROVAL_READY_FLAG <> ''S''
9781: AND AI.INVOICE_TYPE_LOOKUP_CODE<>''INVOICE REQUEST'' /*Bug 16263597*/
9782: AND AI.CANCELLED_DATE IS NULL /* Bug 9777752 */
10041:
10042: --Bug9436217
10043:
10044: FORALL k IN 1..l_selected_invoice_ids.COUNT
10045: UPDATE ap_invoices_all
10046: SET validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
10047: WHERE invoice_id = l_selected_invoice_ids(k)
10048: AND validation_request_id IS NULL;
10049:
10114: ROLLBACK TO SAVEPOINT AP_APPROVAL_PKG_SP_ETAX;
10115:
10116:
10117: FORALL i in 1..l_r_inv_err.count
10118: Update ap_invoices_all set validation_request_id = NULL where invoice_id = l_r_inv_err(i);
10119:
10120: -- Bug 14579876: Moved FND_LOG.String() call above SAVEPOINT
10121: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
10122: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' Rows updated ' || sql%rowcount);
10446: END LOOP;
10447:
10448: --Bug9436217
10449:
10450: UPDATE ap_invoices_all
10451: SET validation_request_id = NULL
10452: WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID;
10453:
10454:
10606:
10607: ROLLBACK TO SAVEPOINT AP_APPROVAL_PKG_SP_TAX_DIST ;
10608:
10609: FORALL i in 1..l_r_inv_err.count
10610: Update ap_invoices_all set validation_request_id = NULL where invoice_id = l_r_inv_err(i);
10611:
10612: -- Bug 14579876: Moved FND_LOG.String() call above SAVEPOINT
10613: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
10614: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' Rows updated ' || sql%rowcount);
10892: END LOOP;
10893:
10894: --Bug9436217
10895:
10896: UPDATE ap_invoices_all
10897: SET validation_request_id = NULL
10898: WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID;
10899:
10900: CLOSE SELECTED_INVOICES_CURSOR ;
11048:
11049: --Bug9436217
11050: -- table alias added for bug 13706647
11051: FORALL blk_upd IN 1..l_selected_invoice_ids.COUNT
11052: UPDATE /*+ index(a AP_INVOICES_U1) */ ap_invoices_all a --hint added for bug 13429649
11053: SET validation_request_id = NULL
11054: WHERE invoice_id = l_selected_invoice_ids( blk_upd )
11055: AND validation_request_id IS NOT NULL;
11056:
11435: p_calling_sequence IN VARCHAR2,
11436: x_error_code IN VARCHAR2,
11437: p_calling_mode IN VARCHAR2 ) IS /*bug 6833543 - added p_calling_mode*/
11438:
11439: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
11440:
11441: l_curr_calling_sequence VARCHAR2(2000);
11442: l_debug_info VARCHAR2(2000);
11443: l_debug_loc VARCHAR2(30) := 'Generate_Distributions';
11830:
11831: --bugfix:5609186 removed the l_recouped_amount from the pay_curr_invoice_amount
11832: --as pay_curr_invoice_amount has nothing to do with recouped amount. Recoupment
11833: --should effect only amount_paid on the invoice.
11834: update ap_invoices_all
11835: set amount_paid = nvl(amount_paid,0) + abs(l_recouped_amount)
11836: ,pay_curr_invoice_amount = ap_utilities_pkg.ap_round_currency
11837: (pay_curr_invoice_amount * payment_cross_rate,
11838: payment_currency_code)
11917: END IF;
11918: END LOOP;
11919:
11920: /* Bug#14582311
11921: l_retained_amount := nvl(ap_invoices_utility_pkg.get_retained_total(p_invoice_rec.invoice_id, null),0);
11922:
11923: IF l_retained_amount <> 0 THEN
11924:
11925: ----------------------------------------------------------------
11933: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
11934: END IF;
11935: ----------------------------------------------------------------
11936:
11937: update ap_invoices_all
11938: set amount_applicable_to_discount = amount_applicable_to_discount + l_retained_amount
11939: ,pay_curr_invoice_amount = ap_utilities_pkg.ap_round_currency
11940: ((invoice_amount + l_retained_amount) * payment_cross_rate,
11941: payment_currency_code)
12176: p_calling_sequence IN VARCHAR2,
12177: x_error_code IN VARCHAR2,
12178: p_calling_mode IN VARCHAR2 ) IS
12179:
12180: t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
12181:
12182: l_curr_calling_sequence VARCHAR2(2000);
12183: l_debug_info VARCHAR2(2000);
12184: l_debug_loc VARCHAR2(30) := 'Generate_Distributions';
12436: END IF;
12437: END LOOP;
12438:
12439: /* Bug#14582311
12440: l_retained_amount := nvl(ap_invoices_utility_pkg.get_retained_total(p_invoice_rec.invoice_id, null),0);
12441:
12442: IF l_retained_amount <> 0 THEN
12443:
12444: ----------------------------------------------------------------
12452: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_debug_loc,l_debug_info);
12453: END IF;
12454: ----------------------------------------------------------------
12455:
12456: UPDATE ap_invoices_all
12457: SET amount_applicable_to_discount = amount_applicable_to_discount
12458: + l_retained_amount
12459: , pay_curr_invoice_amount = ap_utilities_pkg.ap_round_currency(
12460: (invoice_amount + l_retained_amount)
12482: | given vendor and and for the vendors who contains given vendor
12483: | as third party vendor
12484: ===========================================================================*/
12485:
12486: PROCEDURE BATCH_APPROVAL_FOR_VENDOR(P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%type,
12487: P_CALLING_SEQUENCE IN VARCHAR2)is
12488:
12489: l_request_id NUMBER;
12490: l_current_calling_sequence VARCHAR2(2000);
12678:
12679: l_curr_calling_sequence VARCHAR2(2000);
12680: l_debug_loc VARCHAR2(1000) := 'AP_APPROVAL_PKG.POPULATE_GDF_FOR_FV';
12681: l_debug_info VARCHAR2(1000);
12682: l_invoce_type AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
12683:
12684: BEGIN
12685: l_curr_calling_sequence := l_debug_loc|| '<-' ||P_curr_calling_sequence;
12686:
12688: IF g_debug_mode = 'Y' THEN
12689: AP_Debug_Pkg.Print(g_debug_mode, l_debug_info );
12690: END IF;
12691:
12692: IF (AP_INVOICES_UTILITY_PKG.FV_ENABLED) THEN
12693:
12694: ----------------------------------------------------------------
12695: l_debug_info := 'Select invoice type';
12696: IF g_debug_mode = 'Y' THEN
12703: ----------------------------------------------------------------
12704:
12705: SELECT INVOICE_TYPE_LOOKUP_CODE
12706: INTO l_invoce_type
12707: FROM AP_INVOICES
12708: WHERE INVOICE_ID = P_invoice_id;
12709:
12710: IF(l_invoce_type IN ('STANDARD','CREDIT','DEBIT','MIXED','PREPAYMENT'))THEN
12711:
12813: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
12814: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, l_debug_info );
12815: END IF;
12816:
12817: AP_INVOICES_UTILITY_PKG.CHECK_GDF_VALID
12818: (P_INVOICE_ID, --P_id
12819: 'INV_VLD', --P_calling_mode
12820: 'AP_INVOICE_DISTRIBUTIONS', --P_table_name
12821: l_hold_reject_exists_flag, --P_hold_reject_exists_flag