[Home] [Help]
697: BEGIN
698: select count(1)
699: into l_attachments_count
700: from fnd_attached_documents
701: where entity_name = 'AP_INVOICES_INTERFACE'
702: and pk1_value = p_from_invoice_id;
703:
704: -- we only need to copy attachments if there is one
705: if ( l_attachments_count > 0 )
704: -- we only need to copy attachments if there is one
705: if ( l_attachments_count > 0 )
706: then
707: fnd_attached_documents2_pkg.copy_attachments(
708: x_from_entity_name => 'AP_INVOICES_INTERFACE',
709: x_from_pk1_value => p_from_invoice_id,
710: x_to_entity_name => 'AP_INVOICES',
711: x_to_pk1_value => p_to_invoice_id);
712: end if;
706: then
707: fnd_attached_documents2_pkg.copy_attachments(
708: x_from_entity_name => 'AP_INVOICES_INTERFACE',
709: x_from_pk1_value => p_from_invoice_id,
710: x_to_entity_name => 'AP_INVOICES',
711: x_to_pk1_value => p_to_invoice_id);
712: end if;
713:
714: return l_attachments_count;
2737: Sequential Numbering in Invoice Open Interface (R11 only)
2738:
2739: The two modes for numbering can be:
2740: - Simple Manual Entry without Audit: Any value entered in the column
2741: AP_INVOICES_INTERFACE.VOUCHER_NUM will be inserted in AP_INVOICES.
2742: VOUCHER_NUM without validation.
2743:
2744: - Auto Voucher Numbering with Audit: A value will be obtained
2745: automatically for the record being imported and will be populated in
2742: VOUCHER_NUM without validation.
2743:
2744: - Auto Voucher Numbering with Audit: A value will be obtained
2745: automatically for the record being imported and will be populated in
2746: AP_INVOICES. DOC_SEQUENCE_VALUE. Also audit information would be inserted
2747: into the audit table.
2748:
2749: The latter mode will always override the first one.
2750:
2765: If the profile value is "Partial" or "Always" and
2766: the payables option of Invoice Document Category override is
2767: "Yes" then the user can specify the document category, else the
2768: invoice will be rejected for 'Override Disabled', if the user populates
2769: AP_INVOICES_INTERFACE.DOC_CATEGORY_CODE (and override is "No").
2770:
2771: If the profile value is "Always" and no document category is specified
2772: by the user, then "Standard Invoices" category will be used for
2773: standard invoices and "Credit Memo Invoices" category will be used
2796: and the invoice amount is greated than zero then the invoice will be
2797: rejected for 'Invalid Category'.
2798:
2799: The document category specified should be valid in
2800: FND_DOC_SEQUENCE_CATEGORIES for AP_INVOICES or AP_INVOICES_ALL
2801: table. If not then the invoice will be rejected for 'Invalid Category'.
2802:
2803: If the document category is valid then Check the status of the
2804: sequence assigned to this category.The sequence should be automatic
2826: IS
2827: get_doc_seq_failure EXCEPTION;
2828: l_name VARCHAR2(80);
2829: l_doc_category_code
2830: ap_invoices.doc_category_code%TYPE := p_invoice_rec.doc_category_code;
2831: l_application_id NUMBER;
2832: l_doc_seq_ass_id NUMBER;
2833: l_current_invoice_status VARCHAR2(1) := 'Y';
2834: current_calling_sequence VARCHAR2(2000);
2970: SELECT name, application_id
2971: INTO l_name, l_application_id
2972: FROM fnd_doc_sequence_categories
2973: WHERE code = p_invoice_rec.doc_category_code
2974: AND table_name IN ('AP_INVOICES','AP_INVOICES_ALL');
2975: EXCEPTION
2976: WHEN NO_DATA_FOUND THEN
2977: debug_info := debug_info || 'Reject->Doc cat does not exist';
2978: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3467: END get_invoice_info;
3468:
3469: /*=========================================================================
3470:
3471: Function Insert_Ap_Invoices
3472: Program Flow:
3473:
3474: =========================================================================*/
3475: -- Payment Request: Added p_needs_invoice_approval for payment request invoices
3472: Program Flow:
3473:
3474: =========================================================================*/
3475: -- Payment Request: Added p_needs_invoice_approval for payment request invoices
3476: FUNCTION insert_ap_invoices(
3477: p_invoice_rec IN OUT
3478: AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
3479: p_base_invoice_id OUT NOCOPY NUMBER,
3480: p_set_of_books_id IN NUMBER,
3478: AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
3479: p_base_invoice_id OUT NOCOPY NUMBER,
3480: p_set_of_books_id IN NUMBER,
3481: p_doc_sequence_id IN
3482: AP_INVOICES.doc_sequence_id%TYPE,
3483: p_doc_sequence_value IN
3484: AP_INVOICES.doc_sequence_value%TYPE,
3485: p_batch_id IN AP_INVOICES.batch_id%TYPE,
3486: p_pay_curr_invoice_amount IN NUMBER,
3480: p_set_of_books_id IN NUMBER,
3481: p_doc_sequence_id IN
3482: AP_INVOICES.doc_sequence_id%TYPE,
3483: p_doc_sequence_value IN
3484: AP_INVOICES.doc_sequence_value%TYPE,
3485: p_batch_id IN AP_INVOICES.batch_id%TYPE,
3486: p_pay_curr_invoice_amount IN NUMBER,
3487: p_approval_workflow_flag IN VARCHAR2,
3488: p_needs_invoice_approval IN VARCHAR2,
3481: p_doc_sequence_id IN
3482: AP_INVOICES.doc_sequence_id%TYPE,
3483: p_doc_sequence_value IN
3484: AP_INVOICES.doc_sequence_value%TYPE,
3485: p_batch_id IN AP_INVOICES.batch_id%TYPE,
3486: p_pay_curr_invoice_amount IN NUMBER,
3487: p_approval_workflow_flag IN VARCHAR2,
3488: p_needs_invoice_approval IN VARCHAR2,
3489: p_add_days_settlement_date IN NUMBER, --bug 493011
3503:
3504: BEGIN
3505: -- Update the calling sequence
3506:
3507: current_calling_sequence := 'insert_ap_invoices<-'||P_calling_sequence;
3508:
3509: -----------------------------------------------------------------------------
3510: -- Step 1
3511: -- get new invoice_id for base table ap_invoices
3507: current_calling_sequence := 'insert_ap_invoices<-'||P_calling_sequence;
3508:
3509: -----------------------------------------------------------------------------
3510: -- Step 1
3511: -- get new invoice_id for base table ap_invoices
3512: -----------------------------------------------------------------------------
3513:
3514: debug_info := '(Insert ap invoices step 1) Get new invoice_id for base ' ||
3515: 'table ap_invoices';
3510: -- Step 1
3511: -- get new invoice_id for base table ap_invoices
3512: -----------------------------------------------------------------------------
3513:
3514: debug_info := '(Insert ap invoices step 1) Get new invoice_id for base ' ||
3515: 'table ap_invoices';
3516: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3517: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3518: END IF;
3511: -- get new invoice_id for base table ap_invoices
3512: -----------------------------------------------------------------------------
3513:
3514: debug_info := '(Insert ap invoices step 1) Get new invoice_id for base ' ||
3515: 'table ap_invoices';
3516: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3517: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3518: END IF;
3519: -- Bug 5448579
3517: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3518: END IF;
3519: -- Bug 5448579
3520: /*
3521: SELECT ap_invoices_s.nextval
3522: INTO l_invoice_id
3523: FROM sys.dual;
3524: */
3525: -----------------------------------------------------------------------------
3526: -- Step 2
3527: -- get wfapproval_status from profile value - ASP.approval_workflow_flag
3528: -----------------------------------------------------------------------------
3529:
3530: debug_info := '(Insert ap invoices step 2)-Get wfapproval_status ' ||
3531: 'depends on profile value';
3532: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3533: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3534: END IF;
3560: -----------------------------------------------------------------------------
3561: -- Step 3
3562: -- Insert jg_zz_invoice_info
3563: -----------------------------------------------------------------------------
3564: debug_info := '(Insert ap invoices step 3) - Call ' ||
3565: 'jg_globe_flex_val.insert_jg_zz_invoice_info';
3566: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3567: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3568: END IF;
3599:
3600: */
3601:
3602:
3603: debug_info := '(Insert ap invoices step 3) Calculate earliest settlement date for Prepayment type invoices';
3604: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3605: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3606: END IF;
3607:
3611:
3612:
3613: -----------------------------------------------------------------------------
3614: -- Step 4
3615: -- Insert into ap_invoices table
3616: -----------------------------------------------------------------------------
3617:
3618: debug_info := '(Insert ap invoices step 4) - Insert into ap_invoices';
3619: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3614: -- Step 4
3615: -- Insert into ap_invoices table
3616: -----------------------------------------------------------------------------
3617:
3618: debug_info := '(Insert ap invoices step 4) - Insert into ap_invoices';
3619: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3620: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3621: END IF;
3622:
3621: END IF;
3622:
3623: -- Payment Requests: Added party_id, party_site_id,
3624: -- pay_proc_trxn_type_code, payment_function to the insert stmt
3625: INSERT INTO ap_invoices_all(
3626: invoice_id,
3627: org_id,
3628: last_update_date,
3629: last_updated_by,
3770: REMIT_TO_SUPPLIER_SITE,
3771: REMIT_TO_SUPPLIER_SITE_ID,
3772: RELATIONSHIP_ID
3773: )
3774: VALUES (ap_invoices_s.nextval, -- l_invoice_id, Bug 5448579
3775: p_invoice_rec.org_id,
3776: p_invoice_rec.last_update_date,
3777: --bug 6951863 fix -start
3778: --p_invoice_rec.last_update_login,
3939: -----------------------------------------------------------------------------
3940: -- Step 5
3941: -- copy attachment for the invoice
3942: -----------------------------------------------------------------------------
3943: debug_info := '(Insert ap invoices step 5) before copy attachments: '||
3944: 'source = ' || p_invoice_rec.source || ', from_invoice_id = ' ||
3945: p_invoice_rec.invoice_id || ', to_invoice_id = ' || l_invoice_id;
3946: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3947: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3948: END IF;
3949:
3950: l_attachments_count :=
3951: copy_attachments(p_invoice_rec.invoice_id, l_invoice_id);
3952: debug_info := '(Insert ap invoices step 5) copy attachments done: ' ||
3953: l_attachments_count;
3954: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3955: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3956: END IF;
3958: -----------------------------------------------------------------------------
3959: -- Step 6
3960: -- Assign the out parameter for new invoice_id
3961: -----------------------------------------------------------------------------
3962: debug_info := '(Insert ap invoices step 6) - Return the new invoice_id-> ' ||
3963: to_char(l_invoice_id);
3964: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3965: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
3966: END IF;
3985: END IF;
3986: END IF;
3987: RETURN (FALSE);
3988:
3989: END insert_ap_invoices;
3990:
3991: /*======================================================================
3992: Function: Change_invoice_status
3993:
4027: IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4028: Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4029: END IF;
4030:
4031: UPDATE AP_INVOICES_INTERFACE
4032: SET status = p_status
4033: WHERE invoice_id = p_import_invoice_id;
4034:
4035: RETURN(TRUE);
4111: -- as (request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id or request_id is null)
4112:
4113: IF p_group_id IS NULL THEN
4114:
4115: UPDATE AP_INVOICES_INTERFACE
4116: SET status = l_processed
4117: WHERE source = p_source
4118: AND p_group_id is NULL
4119: AND status = l_processing
4119: AND status = l_processing
4120: AND (request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id or request_id is null);
4121: -- bug 7608232
4122:
4123: UPDATE AP_INVOICES_INTERFACE
4124: SET status = l_rejected
4125: WHERE source = p_source
4126: AND p_group_id is NULL
4127: AND status = l_rejecting
4128: AND (request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id or request_id is null);
4129: -- bug 7608232
4130: ELSE
4131:
4132: UPDATE AP_INVOICES_INTERFACE
4133: SET status = l_processed
4134: WHERE source = p_source
4135: AND group_id = p_group_id
4136: AND status = l_processing
4135: AND group_id = p_group_id
4136: AND status = l_processing
4137: AND (request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id or request_id is null);
4138: -- bug 7608232
4139: UPDATE AP_INVOICES_INTERFACE
4140: SET status = l_rejected
4141: WHERE source = p_source
4142: AND group_id = p_group_id
4143: AND status = l_rejecting
4304: END Update_ap_Batches;
4305:
4306: /*=========================================================================*/
4307: /* */
4308: /* Function Insert_Ap_Invoices_lines */
4309: /* Program Flow: */
4310: /* 1. Insert into ap_invoice_lines with the validated interface lines */
4311: /* data */
4312: /* 2. Bulk select primary key of lines */
4352: -- bug# 6989166 ends
4353:
4354: Cursor c_ship_to (c_invoice_id NUMBER) Is
4355: Select aps.ship_to_location_id
4356: From ap_invoices_all ai,
4357: ap_supplier_sites_all aps
4358: Where ai.invoice_id = c_invoice_id
4359: And ai.vendor_site_id = aps.vendor_site_id;
4360:
4473: l_amt_already_billed NUMBER;
4474:
4475: l_modified_line_rounding_amt NUMBER; --6892789
4476: l_base_amt NUMBER; --6892789
4477: l_round_inv_line_numbers AP_INVOICES_UTILITY_PKG.inv_line_num_tab_type; --6892789
4478:
4479: l_start_index NUMBER;
4480: l_end_index NUMBER;
4481:
4558: case, adjust line base amount upto zero and adjust the remaing amount in
4559: another line having next max amount */
4560:
4561: -- get the lines which can be adjusted
4562: l_round_amt_exist := AP_INVOICES_UTILITY_PKG.round_base_amts(
4563: X_Invoice_Id => p_base_invoice_id,
4564: X_Reporting_Ledger_Id => NULL,
4565: X_Rounded_Line_Numbers => l_round_inv_line_numbers,
4566: X_Rounded_Amt => l_rounded_amt,