[Home] [Help]
1: PACKAGE BODY AP_INVOICES_PKG AS
2: /* $Header: apiinceb.pls 120.62.12020000.2 2013/03/11 05:51:42 harsanan ship $ */
3:
4: -- Bug 10103631 - Start
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_INVOICES_PKG';
1: PACKAGE BODY AP_INVOICES_PKG AS
2: /* $Header: apiinceb.pls 120.62.12020000.2 2013/03/11 05:51:42 harsanan ship $ */
3:
4: -- Bug 10103631 - Start
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_INVOICES_PKG';
6: G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
7: G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
8: G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
9: G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
17: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
18: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
19: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
20: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
21: G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_INVOICES_PKG.';
22: -- Bug 10103631 - End
23:
24: PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
25: X_Invoice_Id IN OUT NOCOPY NUMBER,
191: BEGIN
192: -- Update the calling sequence
193: --
194: current_calling_sequence :=
195: 'AP_INVOICES_PKG.INSERT_ROW<-'||X_calling_sequence;
196:
197: -- Get LE Information
198: --
199: IF x_legal_entity_id IS NULL THEN
618: rtrim(ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY,
619: rtrim(APPROVAL_STATUS) APPROVAL_STATUS,
620: rtrim(APPROVAL_DESCRIPTION) APPROVAL_DESCRIPTION,
621: POSTING_STATUS,
622: AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID) POSTING_FLAG,
623: AUTHORIZED_BY,
624: CANCELLED_DATE,
625: CANCELLED_BY,
626: CANCELLED_AMOUNT,
717: RELATIONSHIP_ID,
718: /* Bug 7831073 */
719: original_invoice_amount,
720: rtrim(dispute_reason) dispute_reason
721: FROM ap_invoices_all
722: WHERE rowid = X_Rowid
723: FOR UPDATE of Invoice_Id NOWAIT;
724:
725: Recinfo C%ROWTYPE;
731: BEGIN
732: -- Update the calling sequence
733:
734: current_calling_sequence :=
735: 'AP_INVOICES_PKG.LOCK_ROW<-'||X_calling_sequence;
736:
737: debug_info := 'Open cursor C';
738: OPEN C;
739: debug_info := 'Fetch cursor C';
1382: RELATIONSHIP_ID,
1383: /* Bug 7831073 */
1384: original_invoice_amount,
1385: dispute_reason
1386: FROM ap_invoices_all
1387: WHERE invoice_id = X_Invoice_id
1388: FOR UPDATE of Invoice_Id NOWAIT;
1389:
1390: Recinfo C%ROWTYPE;
1393:
1394: BEGIN
1395: -- Update the calling sequence
1396:
1397: current_calling_sequence := 'AP_INVOICES_PKG.LOCK_ROW(Invoice_id)<-'||
1398: X_calling_sequence;
1399:
1400: debug_info := 'Open cursor C';
1401: OPEN C;
1604: BEGIN
1605:
1606: -- Update the calling sequence
1607:
1608: current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW<-'||
1609: X_calling_sequence;
1610:
1611: -- Bug 10103631
1612: SELECT CASE WHEN NULLIF(X_Invoice_Num,ai.invoice_num) IS NOT NULL
1619: THEN 'TRUE'
1620: ELSE 'FALSE'
1621: END
1622: INTO l_is_update_required
1623: FROM ap_invoices ai
1624: WHERE rowid = X_Rowid ;
1625:
1626: debug_info := 'l_is_update_required = ' || l_is_update_required ;
1627: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1627: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1628: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, debug_info);
1629: END IF;
1630:
1631: debug_info := 'Update ap_invoices';
1632: AP_AI_TABLE_HANDLER_PKG.Update_Row
1633: (X_Rowid,
1634: X_Invoice_Id,
1635: X_Last_Update_Date,
1790:
1791: -- Bug 7570234 Start
1792: -- Bug 13402071: Calling AP_ETAX_SERVICES_PKG.synchronize_for_doc_seq regardless of
1793: -- the invoice status
1794: /* IF (AP_INVOICES_PKG.get_approval_status(
1795: X_Invoice_Id,
1796: X_Invoice_Amount ,
1797: X_Payment_Status_Flag ,
1798: X_Invoice_Type_Lookup_Code)
1835:
1836: l_event_source_info.application_id := 200;
1837: l_event_source_info.legal_entity_id := l_le_id ;
1838: l_event_source_info.ledger_id := X_set_of_books_id;
1839: l_event_source_info.entity_type_code := 'AP_INVOICES';
1840: l_event_source_info.transaction_number := X_invoice_num;
1841: l_event_source_info.source_id_int_1 := X_invoice_id;
1842: l_event_security_context.security_id_int_1 := X_org_id;
1843:
1901: BEGIN
1902:
1903: -- Update the calling sequence
1904: --
1905: current_calling_sequence := 'AP_INVOICES_PKG.DELETE_ROW<-'||
1906: X_calling_sequence;
1907:
1908: -- Get the invoice_id
1909: debug_info := 'Get the invoice_id';
1909: debug_info := 'Get the invoice_id';
1910:
1911: SELECT invoice_id
1912: INTO l_invoice_id
1913: FROM ap_invoices
1914: WHERE rowid = X_rowid;
1915:
1916: -- Verify that the record being deleted meets the requirements
1917: -- for deletion
1916: -- Verify that the record being deleted meets the requirements
1917: -- for deletion
1918: debug_info := 'Get parameter values to check requirements for deletion';
1919: SELECT
1920: ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1917: -- for deletion
1918: debug_info := 'Get parameter values to check requirements for deletion';
1919: SELECT
1920: ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1918: debug_info := 'Get parameter values to check requirements for deletion';
1919: SELECT
1920: ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1919: SELECT
1920: ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1927: ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1920: ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1927: ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1928: payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
1921: ap_invoices_pkg.get_encumbered_flag(invoice_id),
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1927: ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1928: payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
1929: invoice_type_lookup_code
1922: ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1927: ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1928: payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
1929: invoice_type_lookup_code
1930: INTO
1923: ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1924: ap_invoices_pkg.get_posting_status(invoice_id),
1925: ap_invoices_pkg.get_po_number(invoice_id),
1926: ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1927: ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1928: payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
1929: invoice_type_lookup_code
1930: INTO
1931: l_prepayments_applied_flag,
1936: l_po_number,
1937: l_prepay_amount_applied,
1938: l_approval_status,
1939: l_invoice_type
1940: FROM ap_invoices
1941: WHERE rowid = X_Rowid;
1942:
1943: IF (l_prepayments_applied_flag = 'Y') THEN
1944: l_message_name := 'AP_INV_DEL_INV_PREPAYS';
2005:
2006: -----------------------------------------------------------------------
2007: -- FUNCTION get_expenditure_item_date RETURNs the expenditure item date
2008: -- to be used given PA's profile option: 'PA: Default Expenditure Item
2009: -- Date for AP Invoices'
2010: -----------------------------------------------------------------------
2011:
2012: FUNCTION Get_Expenditure_Item_Date(
2013: X_invoice_id IN NUMBER,
2126:
2127: END get_expenditure_item_date;
2128:
2129: --=========================================================================
2130: -- The following functions have been mapped to AP_INVOICES_UTILITY_PKG
2131: -- (apinvuts.pls apinvutb.pls)
2132: --
2133: --=========================================================================
2134: FUNCTION get_distribution_total(l_invoice_id IN NUMBER)
2136: IS
2137: l_distribution_total NUMBER := 0;
2138:
2139: BEGIN
2140: l_distribution_total := AP_INVOICES_UTILITY_PKG.get_distribution_total(
2141: l_invoice_id);
2142:
2143: RETURN(l_distribution_total);
2144:
2151: IS
2152: l_invoice_posting_flag VARCHAR2(1);
2153:
2154: BEGIN
2155: l_invoice_posting_flag := AP_INVOICES_UTILITY_PKG.get_posting_status(
2156: l_invoice_id);
2157:
2158: RETURN(l_invoice_posting_flag);
2159:
2168: X_VENDOR_SITE_ID NUMBER, /*Bug9105666*/
2169: X_calling_sequence IN VARCHAR2) IS
2170: BEGIN
2171:
2172: AP_INVOICES_UTILITY_PKG.CHECK_UNIQUE (
2173: X_ROWID,
2174: X_INVOICE_NUM,
2175: X_VENDOR_ID,
2176: X_ORG_ID, /* Bug 5407785 */
2190: X_VOUCHER_NUM VARCHAR2,
2191: X_calling_sequence IN VARCHAR2) IS
2192: BEGIN
2193:
2194: AP_INVOICES_UTILITY_PKG.CHECK_UNIQUE_VOUCHER_NUM (
2195: X_ROWID,
2196: X_VOUCHER_NUM,
2197: X_calling_sequence);
2198:
2211: IS
2212: l_invoice_approval_status VARCHAR2(25);
2213: BEGIN
2214:
2215: l_invoice_approval_status := AP_INVOICES_UTILITY_PKG.get_approval_status(
2216: l_invoice_id,
2217: l_invoice_amount,
2218: l_payment_status_flag,
2219: l_invoice_type_lookup_code);
2226: RETURN VARCHAR2
2227: IS
2228: l_po_number VARCHAR2(50); -- for CLM Bug 9503239
2229: BEGIN
2230: l_po_number := AP_INVOICES_UTILITY_PKG.get_po_number(l_invoice_id);
2231:
2232: RETURN(l_po_number);
2233:
2234: END get_po_number;
2237: RETURN VARCHAR2
2238: IS
2239: l_release_number VARCHAR2(25);
2240: BEGIN
2241: l_release_number := AP_INVOICES_UTILITY_PKG.get_release_number(l_invoice_id);
2242:
2243: RETURN(l_release_number);
2244:
2245: END get_release_number;
2249: RETURN VARCHAR2
2250: IS
2251: l_receipt_number RCV_SHIPMENT_HEADERS.RECEIPT_NUM%TYPE; --Bug 16413390
2252: BEGIN
2253: l_receipt_number := AP_INVOICES_UTILITY_PKG.get_receipt_number(l_invoice_id);
2254:
2255: RETURN(l_receipt_number);
2256:
2257: END get_receipt_number;
2261: IS
2262: l_po_number_list VARCHAR2(5000) := NULL; -- for CLM Bug 9503239
2263: BEGIN
2264:
2265: l_po_number_list := AP_INVOICES_UTILITY_PKG.get_po_number_list(l_invoice_id);
2266:
2267: RETURN(l_po_number_list);
2268:
2269: END get_po_number_list;
2272: RETURN NUMBER
2273: IS
2274: l_amount_withheld NUMBER := 0;
2275: BEGIN
2276: l_amount_withheld := AP_INVOICES_UTILITY_PKG.get_amount_withheld(
2277: l_invoice_id);
2278:
2279: RETURN(l_amount_withheld);
2280:
2284: RETURN NUMBER
2285: IS
2286: l_prepaid_amount NUMBER := 0;
2287: BEGIN
2288: l_prepaid_amount := AP_INVOICES_UTILITY_PKG.get_prepaid_amount(l_invoice_id);
2289:
2290: RETURN(l_prepaid_amount);
2291:
2292: END get_prepaid_amount;
2295: RETURN NUMBER
2296: IS
2297: l_notes_count NUMBER := 0;
2298: BEGIN
2299: l_notes_count := AP_INVOICES_UTILITY_PKG.get_notes_count(l_invoice_id);
2300: RETURN(l_notes_count);
2301:
2302: END get_notes_count;
2303:
2305: RETURN NUMBER
2306: IS
2307: l_holds_count NUMBER := 0;
2308: BEGIN
2309: l_holds_count := AP_INVOICES_UTILITY_PKG.get_holds_count(l_invoice_id);
2310:
2311: RETURN(l_holds_count);
2312:
2313: END get_holds_count;
2317: RETURN NUMBER
2318: IS
2319: l_holds_count NUMBER := 0;
2320: BEGIN
2321: l_holds_count := AP_INVOICES_UTILITY_PKG.get_sched_holds_count(l_invoice_id);
2322:
2323: RETURN(l_holds_count);
2324:
2325: END get_sched_holds_count;
2332: IS
2333: l_prepay_count NUMBER := 0;
2334: BEGIN
2335: -- MOAC. Added org_id parameter to FUNCTION and to call
2336: l_prepay_count := AP_INVOICES_UTILITY_PKG.get_total_prepays(
2337: l_vendor_id,
2338: l_org_id);
2339: RETURN(l_prepay_count);
2340:
2347: IS
2348: l_prepay_count NUMBER := 0;
2349: BEGIN
2350: -- MOAC. Added org_id parameter to FUNCTION and to call
2351: l_prepay_count := AP_INVOICES_UTILITY_PKG.get_available_prepays(
2352: l_vendor_id,
2353: l_org_id);
2354:
2355: RETURN(l_prepay_count);
2360: IS
2361: l_encumbered_flag VARCHAR2(1);
2362:
2363: BEGIN
2364: l_encumbered_flag := AP_INVOICES_UTILITY_PKG.get_encumbered_flag(
2365: l_invoice_id);
2366:
2367: RETURN(l_encumbered_flag);
2368:
2372: RETURN VARCHAR2
2373: IS
2374: l_amount_hold_flag VARCHAR2(1) := 'N';
2375: BEGIN
2376: l_amount_hold_flag := AP_INVOICES_UTILITY_PKG.get_amount_hold_flag(
2377: l_invoice_id);
2378:
2379: RETURN(L_amount_hold_flag);
2380:
2384: RETURN VARCHAR2
2385: IS
2386: l_vendor_hold_flag VARCHAR2(1) := 'N';
2387: BEGIN
2388: l_vendor_hold_flag := AP_INVOICES_UTILITY_PKG.get_vendor_hold_flag(
2389: l_invoice_id);
2390:
2391: RETURN(l_vendor_hold_flag);
2392:
2395: -- --------------------------------------------------------------------------
2396: -- Procedure get_gl_date_and_period() can be used to determine the
2397: -- open period given a date. This PROCEDURE will also allow you to
2398: -- compare with a parent GL date, such as that of ap_batches for
2399: -- ap_invoices. You needn't pass a parent date, however, to determine
2400: -- the next open period. The GL date and period name are written to
2401: -- IN OUT NOCOPY parameters, P_GL_Date and P_Period_Name, passed to the
2402: -- procedure. If there is no open period, the PROCEDURE RETURNs
2403: -- null in the IN OUT NOCOPY parameters.
2400: -- the next open period. The GL date and period name are written to
2401: -- IN OUT NOCOPY parameters, P_GL_Date and P_Period_Name, passed to the
2402: -- procedure. If there is no open period, the PROCEDURE RETURNs
2403: -- null in the IN OUT NOCOPY parameters.
2404: -- Instead of Calling the AP_INVOICES_UTILITY_PKG.get_gl_date_and_period,
2405: -- the code has been SHIFTED into this PROCEDURE as a part of clean-up and
2406: -- PROCEDURE get_gl_date_and_period is REMOVED from AP_INVOICE_UTILITY_PKG.
2407: -- -------------------------------------------------------------------------
2408:
2493: l_period_name gl_period_statuses.period_name%TYPE := ''; -- Not used
2494: BEGIN
2495:
2496: -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2497: -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2498:
2499: ap_invoices_pkg.get_gl_date_and_period( -- get_gl_date
2500: l_invoice_date,
2501: l_receipt_date,
2495:
2496: -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2497: -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2498:
2499: ap_invoices_pkg.get_gl_date_and_period( -- get_gl_date
2500: l_invoice_date,
2501: l_receipt_date,
2502: l_period_name,
2503: l_GL_Date);
2516: l_period_name gl_period_statuses.period_name%TYPE := '';
2517: BEGIN
2518:
2519: -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2520: -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2521: -- MOAC. Added org_id parameter to FUNCTION and call
2522:
2523: ap_invoices_pkg.get_gl_date_and_period(
2524: P_date => l_invoice_date,
2519: -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2520: -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2521: -- MOAC. Added org_id parameter to FUNCTION and call
2522:
2523: ap_invoices_pkg.get_gl_date_and_period(
2524: P_date => l_invoice_date,
2525: P_Receipt_Date => l_receipt_date,
2526: P_Period_Name => l_period_name,
2527: P_GL_Date => l_GL_Date,
2537: P_invoice_type_lookup_code IN VARCHAR2,
2538: P_invoice_currency_code IN VARCHAR2,
2539: P_calling_sequence IN VARCHAR2) RETURN VARCHAR2
2540: IS
2541: l_invoice_num ap_invoices.invoice_num%TYPE;
2542:
2543: BEGIN
2544: l_invoice_num := AP_INVOICES_UTILITY_PKG.get_similar_drcr_memo(
2545: P_vendor_id,
2540: IS
2541: l_invoice_num ap_invoices.invoice_num%TYPE;
2542:
2543: BEGIN
2544: l_invoice_num := AP_INVOICES_UTILITY_PKG.get_similar_drcr_memo(
2545: P_vendor_id,
2546: P_vendor_site_id,
2547: P_invoice_amount,
2548: P_invoice_type_lookup_code,
2557: P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
2558: IS
2559: l_flag boolean;
2560: BEGIN
2561: l_flag := AP_INVOICES_UTILITY_PKG.eft_bank_details_exist (
2562: P_vendor_site_id,
2563: P_calling_sequence);
2564:
2565: RETURN(l_flag);
2571: P_calling_sequence IN VARCHAR2) RETURN boolean
2572: IS
2573: l_flag boolean;
2574: BEGIN
2575: l_flag := AP_INVOICES_UTILITY_PKG.eft_bank_curr_details_exist (
2576: P_vendor_site_id,
2577: P_currency_code,
2578: P_calling_sequence);
2579:
2585: P_invoice_id IN NUMBER) RETURN VARCHAR2
2586: IS
2587: l_flag VARCHAR2(1) := 'N';
2588: BEGIN
2589: l_flag := AP_INVOICES_UTILITY_PKG.selected_for_payment_flag (P_invoice_id);
2590:
2591: RETURN(l_flag);
2592:
2593: END selected_for_payment_flag;
2596: RETURN VARCHAR2
2597: IS
2598: l_flag VARCHAR2(1) := 'N';
2599: BEGIN
2600: l_flag := AP_INVOICES_UTILITY_PKG.get_discount_pay_dists_flag (P_invoice_id);
2601:
2602: RETURN(l_flag);
2603:
2604: END get_discount_pay_dists_flag;
2609: RETURN VARCHAR2
2610: IS
2611: l_flag VARCHAR2(1) := 'N';
2612: BEGIN
2613: l_flag := AP_INVOICES_UTILITY_PKG.get_unposted_void_payment (P_invoice_id);
2614:
2615: RETURN(l_flag);
2616:
2617: END get_unposted_void_payment;
2621: RETURN VARCHAR2
2622: IS
2623: l_flag VARCHAR2(1) := 'N';
2624: BEGIN
2625: l_flag := AP_INVOICES_UTILITY_PKG.get_prepayments_applied_flag (P_invoice_id);
2626: RETURN(l_flag);
2627:
2628: END get_prepayments_applied_flag;
2629:
2632: RETURN VARCHAR2
2633: IS
2634: l_flag VARCHAR2(1) := 'N';
2635: BEGIN
2636: l_flag := AP_INVOICES_UTILITY_PKG.get_payments_exist_flag (P_invoice_id);
2637:
2638: RETURN(l_flag);
2639:
2640: END get_payments_exist_flag;
2644: IS
2645: l_prepay_amount NUMBER := 0;
2646:
2647: BEGIN
2648: l_prepay_amount := AP_INVOICES_UTILITY_PKG.get_prepay_amount_applied (
2649: P_invoice_id);
2650:
2651: RETURN(l_prepay_amount);
2652:
2656: RETURN NUMBER
2657: IS
2658: l_packet_id NUMBER := '';
2659: BEGIN
2660: l_packet_id := AP_INVOICES_UTILITY_PKG.get_packet_id (P_invoice_id);
2661:
2662: RETURN(l_packet_id);
2663:
2664: END get_packet_id;
2663:
2664: END get_packet_id;
2665:
2666: --=========================================================================
2667: -- The functions above have been mapped to AP_INVOICES_UTILITY_PKG
2668: -- (apinvuts.pls apinvutb.pls)
2669: --=========================================================================
2670:
2671: --=========================================================================
2668: -- (apinvuts.pls apinvutb.pls)
2669: --=========================================================================
2670:
2671: --=========================================================================
2672: -- The Following functions have been mapped to AP_INVOICES_POST_PROCESS_PKG
2673: -- (apinvpps.pls apinvppb.pls)
2674: --
2675: --=========================================================================
2676:
2691: X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
2692:
2693: IS
2694: BEGIN
2695: AP_INVOICES_POST_PROCESS_PKG.insert_children (
2696: X_invoice_id,
2697: X_Payment_Priority,
2698: X_Hold_count,
2699: X_Line_count,
2719: X_vendor_changed_flag IN VARCHAR2 DEFAULT 'N',
2720: X_calling_sequence IN VARCHAR2)
2721: IS
2722: BEGIN
2723: AP_INVOICES_POST_PROCESS_PKG.create_holds (
2724: X_invoice_id,
2725: X_event,
2726: X_update_base,
2727: X_vendor_changed_flag,
2768: X_calling_sequence IN VARCHAR2,
2769: X_revalidate_ps IN OUT NOCOPY VARCHAR2)
2770: IS
2771: BEGIN
2772: AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update (
2773: X_invoice_id,
2774: X_invoice_amount,
2775: X_payment_status_flag,
2776: X_invoice_type_lookup_code,
2818: X_calling_sequence IN VARCHAR2,
2819: X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
2820: IS
2821: BEGIN
2822: AP_INVOICES_POST_PROCESS_PKG.invoice_post_update (
2823: X_invoice_id,
2824: X_payment_priority,
2825: X_recalc_pay_sched,
2826: X_Hold_count,
2869: X_sched_holds_count IN OUT NOCOPY NUMBER) IS --bug 5334577
2870:
2871: BEGIN
2872:
2873: AP_INVOICES_POST_PROCESS_PKG.post_forms_commit (
2874: X_invoice_id,
2875: NULL,
2876: X_type_1099,
2877: X_income_tax_region,
2909: X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
2910: X_Calling_Sequence IN VARCHAR2)
2911: IS
2912: BEGIN
2913: AP_INVOICES_POST_PROCESS_PKG.Select_Summary(
2914: X_Batch_ID,
2915: X_Total,
2916: X_Total_Rtot_DB,
2917: X_Calling_Sequence);
2921: APP_EXCEPTION.RAISE_EXCEPTION;
2922: END Select_Summary;
2923:
2924: --=========================================================================
2925: -- The Functions above have been mapped to AP_INVOICES_POST_PROCESS_PKG
2926: -- (apinvpps.pls apinvppb.pls)
2927: --=========================================================================
2928:
2929: --bug4299234
2958: If l_reapprove>0 then return('NEEDS WFREAPPROVAL'); end if; /* Bug 11655111 */
2959:
2960: select wfapproval_status
2961: into header_approval_status
2962: from ap_invoices_all
2963: where invoice_id=p_invoice_id
2964: and org_id=p_org_id;
2965:
2966: IF (l_approved > 0 and header_approval_status = 'NOT REQUIRED') THEN
3125: sysdate,
3126: 1,
3127: aps.iby_hold_reason,
3128: aps.hold_flag
3129: from ap_invoices_all ai,
3130: ap_payment_schedules_all aps,
3131: ap_suppliers pv,
3132: ap_supplier_sites_all pvs
3133: where ai.invoice_id = p_invoice_id
3143: l_DOCUMENT_PAYABLE_ID number;
3144: l_CALLING_APP_ID number;
3145: l_CALLING_APP_DOC_UNIQUE_REF1 number;
3146: l_CALLING_APP_DOC_UNIQUE_REF2 number;
3147: -- l_CALLING_APP_DOC_REF_NUMBER ap_invoices_all.invoice_num%type;
3148: l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3149: l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3144: l_CALLING_APP_ID number;
3145: l_CALLING_APP_DOC_UNIQUE_REF1 number;
3146: l_CALLING_APP_DOC_UNIQUE_REF2 number;
3147: -- l_CALLING_APP_DOC_REF_NUMBER ap_invoices_all.invoice_num%type;
3148: l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3149: l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3145: l_CALLING_APP_DOC_UNIQUE_REF1 number;
3146: l_CALLING_APP_DOC_UNIQUE_REF2 number;
3147: -- l_CALLING_APP_DOC_REF_NUMBER ap_invoices_all.invoice_num%type;
3148: l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3149: l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3153: l_DOCUMENT_DATE date;
3147: -- l_CALLING_APP_DOC_REF_NUMBER ap_invoices_all.invoice_num%type;
3148: l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3149: l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3153: l_DOCUMENT_DATE date;
3154: l_DOCUMENT_TYPE ap_invoices_all.invoice_type_lookup_code%type;
3155: l_DOCUMENT_DESCRIPTION ap_invoices_all.description%type;
3148: l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3149: l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3153: l_DOCUMENT_DATE date;
3154: l_DOCUMENT_TYPE ap_invoices_all.invoice_type_lookup_code%type;
3155: l_DOCUMENT_DESCRIPTION ap_invoices_all.description%type;
3156: l_DOCUMENT_AMOUNT number;
3150: l_PAYMENT_AMOUNT number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3153: l_DOCUMENT_DATE date;
3154: l_DOCUMENT_TYPE ap_invoices_all.invoice_type_lookup_code%type;
3155: l_DOCUMENT_DESCRIPTION ap_invoices_all.description%type;
3156: l_DOCUMENT_AMOUNT number;
3157: l_EXTERNAL_BANK_ACCOUNT_ID number;
3158: l_PAYEE_PARTY_ID number;
3151: l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3152: l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3153: l_DOCUMENT_DATE date;
3154: l_DOCUMENT_TYPE ap_invoices_all.invoice_type_lookup_code%type;
3155: l_DOCUMENT_DESCRIPTION ap_invoices_all.description%type;
3156: l_DOCUMENT_AMOUNT number;
3157: l_EXTERNAL_BANK_ACCOUNT_ID number;
3158: l_PAYEE_PARTY_ID number;
3159: l_PAYEE_PARTY_SITE_ID number;
3160: l_SUPPLIER_SITE_ID number;
3161: l_LEGAL_ENTITY_ID number;
3162: l_ORG_ID number;
3163: l_ORG_TYPE varchar2(30);
3164: l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3161: l_LEGAL_ENTITY_ID number;
3162: l_ORG_ID number;
3163: l_ORG_TYPE varchar2(30);
3164: l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3162: l_ORG_ID number;
3163: l_ORG_TYPE varchar2(30);
3164: l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3163: l_ORG_TYPE varchar2(30);
3164: l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3164: l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3172: l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3165: l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3166: l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3167: l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3168: l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3172: l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3173: l_UNIQUE_REMITTANCE_IDENTIFIER ap_invoices_all.unique_remittance_identifier%type;
3169: l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3172: l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3173: l_UNIQUE_REMITTANCE_IDENTIFIER ap_invoices_all.unique_remittance_identifier%type;
3174: l_URI_CHECK_DIGIT ap_invoices_all.uri_check_digit%type;
3175: l_DELIVERY_CHANNEL_CODE ap_invoices_all.delivery_channel_code%type;
3176: l_DISCOUNT_DATE date;
3177: l_CREATED_BY number;
3170: l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3172: l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3173: l_UNIQUE_REMITTANCE_IDENTIFIER ap_invoices_all.unique_remittance_identifier%type;
3174: l_URI_CHECK_DIGIT ap_invoices_all.uri_check_digit%type;
3175: l_DELIVERY_CHANNEL_CODE ap_invoices_all.delivery_channel_code%type;
3176: l_DISCOUNT_DATE date;
3177: l_CREATED_BY number;
3178: l_CREATION_DATE date;
3171: l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3172: l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3173: l_UNIQUE_REMITTANCE_IDENTIFIER ap_invoices_all.unique_remittance_identifier%type;
3174: l_URI_CHECK_DIGIT ap_invoices_all.uri_check_digit%type;
3175: l_DELIVERY_CHANNEL_CODE ap_invoices_all.delivery_channel_code%type;
3176: l_DISCOUNT_DATE date;
3177: l_CREATED_BY number;
3178: l_CREATION_DATE date;
3179: l_LAST_UPDATED_BY number;
3406: l_invoice_includes_prepay_flag VARCHAR2(1);
3407: BEGIN
3408: SELECT 'Y'
3409: INTO l_invoice_includes_prepay_flag
3410: FROM ap_invoices_all ai
3411: WHERE ai.invoice_id = P_invoice_id
3412: AND EXISTS (SELECT ail.invoice_includes_prepay_flag
3413: FROM ap_invoice_lines_all ail
3414: WHERE ail.invoice_id = ai.invoice_id
3445: l_count NUMBER;
3446: BEGIN
3447: SELECT COUNT(1)
3448: INTO l_count
3449: FROM ap_invoices_all ai
3450: WHERE ai.invoice_id = P_invoice_id
3451: AND ai.historical_flag = 'Y'
3452: AND ai.payment_status_flag = 'Y'
3453: AND NVL(ai.force_revalidation_flag,'N') = 'N'
3500:
3501: If l_count > 0 then
3502: P_force_revalidation_flag := 'Y';
3503: If(P_event = 'ON-INSERT') then
3504: update ap_invoices_all
3505: set force_revalidation_flag = 'Y'
3506: where invoice_id = p_invoice_id;
3507: end if;
3508: END IF;
3509:
3510: END Get_Force_Revalidation_Flag;
3511:
3512:
3513: END AP_INVOICES_PKG;