328:
329: --****************************************************************************************--
330: --* Name : insert_invoice_hdr *--
331: --* Type : Procedure *--
332: --* Purpose : Inserts the Invoice Header record into ap_invoices_interface *--
333: --* Parameters : p_invoice_hdr_rec The Header record that has to be inserted *--
334: --* : p_errbuf Error returned to the concurrent process *--
335: --* : p_retcode Return Code to concurrent process *--
336: --* Global Vars : g_module_name READ *--
344: --* : g_exit READ *--
345: --* Called from : save_or_erase_invoice *--
346: --* Calls : fv_utility.debug_mesg *--
347: --* : fv_utility.log_mesg *--
348: --* Tables Used : ap_invoices_interface INSERT *--
349: --* Logic : Insert the record p_invoice_hdr_rec into table ap_invoices_interface *--
350: --****************************************************************************************--
351: PROCEDURE insert_invoice_hdr
352: (
345: --* Called from : save_or_erase_invoice *--
346: --* Calls : fv_utility.debug_mesg *--
347: --* : fv_utility.log_mesg *--
348: --* Tables Used : ap_invoices_interface INSERT *--
349: --* Logic : Insert the record p_invoice_hdr_rec into table ap_invoices_interface *--
350: --****************************************************************************************--
351: PROCEDURE insert_invoice_hdr
352: (
353: p_invoice_hdr_rec IN ap_invoices_interface%ROWTYPE,
349: --* Logic : Insert the record p_invoice_hdr_rec into table ap_invoices_interface *--
350: --****************************************************************************************--
351: PROCEDURE insert_invoice_hdr
352: (
353: p_invoice_hdr_rec IN ap_invoices_interface%ROWTYPE,
354: p_error_code OUT NOCOPY NUMBER,
355: p_error_desc OUT NOCOPY VARCHAR2
356: )
357: IS
364: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
365: END IF;
366:
367: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
368: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into ap_invoices_interface');
369: END IF;
370: INSERT INTO ap_invoices_interface
371: (
372: invoice_id,
366:
367: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
368: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into ap_invoices_interface');
369: END IF;
370: INSERT INTO ap_invoices_interface
371: (
372: invoice_id,
373: invoice_num,
374: invoice_type_lookup_code,
893: --* Called from : main *--
894: --* Calls : insert_error *--
895: --* : fv_utility.debug_mesg *--
896: --* : fv_utility.log_mesg *--
897: --* Tables Used : ap_invoices_interface SELECT *--
898: --* : ap_invoice_lines_interface SELECT *--
899: --* : ap_interface_rejections SELECT *--
900: --* : fv_ipac_import SELECT, UPDATE *--
901: --* : ap_lookup_codes SELECT *--
909: --* : error status. *--
910: --****************************************************************************************--
911: PROCEDURE check_for_ap_import_errors
912: (
913: p_group_id ap_invoices_interface.group_id%TYPE,
914: p_error_code OUT NOCOPY NUMBER,
915: p_error_desc OUT NOCOPY VARCHAR2
916: )
917: IS
934: FOR invoices_rec IN (SELECT aii.invoice_id,
935: air.reject_lookup_code,
936: fii.ipac_import_id,
937: alc.description
938: FROM ap_invoices_interface aii,
939: ap_interface_rejections air,
940: fv_ipac_import fii,
941: ap_lookup_codes alc
942: WHERE aii.group_id = p_group_id
941: ap_lookup_codes alc
942: WHERE aii.group_id = p_group_id
943: AND aii.status = 'REJECTED'
944: AND aii.invoice_id = air.parent_id
945: AND air.parent_table = 'AP_INVOICES_INTERFACE'
946: AND fii.group_id = p_group_id
947: AND fii.int_invoice_id = aii.invoice_id
948: AND alc.lookup_type = 'REJECT CODE'
949: AND alc.lookup_code = air.reject_lookup_code) LOOP
1006: FOR invoice_lines_rec IN (SELECT aii.invoice_id,
1007: air.reject_lookup_code,
1008: fii.ipac_import_id,
1009: alc.description
1010: FROM ap_invoices_interface aii,
1011: ap_invoice_lines_interface aili,
1012: ap_interface_rejections air,
1013: fv_ipac_import fii,
1014: ap_lookup_codes alc
2774: --* : inserted, delete them from ap_invoice_lines_interface *--
2775: --****************************************************************************************--
2776: PROCEDURE save_or_erase_invoice
2777: (
2778: p_ap_inv_hdr_rec IN ap_invoices_interface%ROWTYPE,
2779: p_previous_inv_number IN ap_invoices_interface.invoice_num%TYPE,
2780: p_okay_to_insert_inv IN VARCHAR2,
2781: p_total_invoice_lines IN NUMBER,
2782: p_total_invoices IN OUT NOCOPY NUMBER,
2775: --****************************************************************************************--
2776: PROCEDURE save_or_erase_invoice
2777: (
2778: p_ap_inv_hdr_rec IN ap_invoices_interface%ROWTYPE,
2779: p_previous_inv_number IN ap_invoices_interface.invoice_num%TYPE,
2780: p_okay_to_insert_inv IN VARCHAR2,
2781: p_total_invoice_lines IN NUMBER,
2782: p_total_invoices IN OUT NOCOPY NUMBER,
2783: p_error_code OUT NOCOPY NUMBER,
2899: --* : resolve_uom *--
2900: --* : insert_invoice_line *--
2901: --* : fv_utility.debug_mesg *--
2902: --* : fv_utility.log_mesg *--
2903: --* Sequence Used : ap_invoices_interface_s *--
2904: --* Tables Used : fv_ipac_import SELECT, UPDATE *--
2905: --* Logic : 1. Pre process the data to get the group_id handle *--
2906: --* : 2. Process every record from fv_ipac_import table *--
2907: --* : 3. Validate the PO. *--
2922: IS
2923: l_module_name VARCHAR2(200);
2924: l_location VARCHAR2(200);
2925: l_ipac_import_record fv_ipac_import%ROWTYPE;
2926: l_inv_hdrs_interface_rec ap_invoices_interface%ROWTYPE;
2927: l_inv_lines_interface_rec ap_invoice_lines_interface%ROWTYPE;
2928: l_inv_hdrs_interface_rec_null ap_invoices_interface%ROWTYPE;
2929: l_inv_lines_interface_rec_null ap_invoice_lines_interface%ROWTYPE;
2930: l_save_invoice_number fv_ipac_import.invoice_number%TYPE;
2924: l_location VARCHAR2(200);
2925: l_ipac_import_record fv_ipac_import%ROWTYPE;
2926: l_inv_hdrs_interface_rec ap_invoices_interface%ROWTYPE;
2927: l_inv_lines_interface_rec ap_invoice_lines_interface%ROWTYPE;
2928: l_inv_hdrs_interface_rec_null ap_invoices_interface%ROWTYPE;
2929: l_inv_lines_interface_rec_null ap_invoice_lines_interface%ROWTYPE;
2930: l_save_invoice_number fv_ipac_import.invoice_number%TYPE;
2931: l_ok_to_insert_inv VARCHAR2(1);
2932: l_current_inv_lines NUMBER;
3080: BEGIN
3081: --****************************************************************************************--
3082: --* get the invoice header sequence number *--
3083: --****************************************************************************************--
3084: SELECT ap_invoices_interface_s.NEXTVAL
3085: INTO l_inv_hdrs_interface_rec.invoice_id
3086: FROM DUAL;
3087: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3088: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id ='||l_inv_hdrs_interface_rec.invoice_id);
3090: EXCEPTION
3091: WHEN OTHERS THEN
3092: p_error_code := g_FAILURE;
3093: p_error_desc := SQLERRM;
3094: l_location := l_module_name||'select_ap_invoices_interface_s';
3095: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3096: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3097: END;
3098: END IF;
3446:
3447: --****************************************************************************************--
3448: --* Name : cleanup_current_failed_run *--
3449: --* Type : Procedure *--
3450: --* Purpose : This procedure removes data from ap_invoices_interface, *--
3451: --* : ap_invoice_lines_interface and ap_interface_rejections after a *--
3452: --* : failed run *--
3453: --* Parameters : p_group_id IN The group id for which the data is processed *--
3454: --* : p_errbuf OUT Error returned to the concurrent process *--
3467: --* : fv_utility.log_mesg *--
3468: --* Tables Used : fv_ipac_import SELECT, UPDATE *--
3469: --* : ap_interface_rejections DELETE *--
3470: --* : ap_invoice_lines_interface DELETE *--
3471: --* : ap_invoices_interface DELETE *--
3472: --* Logic : For each invoice number with status of ERROR in fv_ipac_import table *--
3473: --* : do the following *--
3474: --* : 1. Delete all data from ap_interface_rejections for lines *--
3475: --* : 2. Delete all data from ap_interface_rejections for header *--
3473: --* : do the following *--
3474: --* : 1. Delete all data from ap_interface_rejections for lines *--
3475: --* : 2. Delete all data from ap_interface_rejections for header *--
3476: --* : 3. Delete all data from ap_invoice_lines_interface *--
3477: --* : 4. Delete all data from ap_invoices_interface *--
3478: --* : 5. Update all other records of fv_ipac_import table with same *--
3479: --* : invoice and status of not ERROR to ERROR IN OTHER LINES *--
3480: --****************************************************************************************--
3481: PROCEDURE cleanup_current_failed_run
3520: DELETE ap_interface_rejections
3521: WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
3522: AND parent_id IN (SELECT invoice_line_id
3523: FROM ap_invoice_lines_interface aili,
3524: ap_invoices_interface aii
3525: WHERE aii.invoice_num = import_rec.invoice_number
3526: AND aii.group_id = p_group_id
3527: AND aii.invoice_id = aili.invoice_id);
3528: l_rowcount := SQL%ROWCOUNT;
3544: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3545: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_interface_rejections2');
3546: END IF;
3547: DELETE ap_interface_rejections
3548: WHERE parent_table = 'AP_INVOICES_INTERFACE'
3549: AND parent_id IN (SELECT invoice_id
3550: FROM ap_invoices_interface aii
3551: WHERE aii.invoice_num = import_rec.invoice_number
3552: AND aii.group_id = p_group_id);
3546: END IF;
3547: DELETE ap_interface_rejections
3548: WHERE parent_table = 'AP_INVOICES_INTERFACE'
3549: AND parent_id IN (SELECT invoice_id
3550: FROM ap_invoices_interface aii
3551: WHERE aii.invoice_num = import_rec.invoice_number
3552: AND aii.group_id = p_group_id);
3553: l_rowcount := SQL%ROWCOUNT;
3554: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3570: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoice_lines_interface');
3571: END IF;
3572: DELETE ap_invoice_lines_interface
3573: WHERE invoice_id IN (SELECT invoice_id
3574: FROM ap_invoices_interface
3575: WHERE invoice_num = import_rec.invoice_number
3576: AND group_id = p_group_id);
3577: l_rowcount := SQL%ROWCOUNT;
3578: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3591:
3592: IF (p_error_code = g_SUCCESS) THEN
3593: BEGIN
3594: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3595: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoices_interface');
3596: END IF;
3597: DELETE ap_invoices_interface
3598: WHERE invoice_num = import_rec.invoice_number
3599: AND group_id = p_group_id;
3593: BEGIN
3594: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3595: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoices_interface');
3596: END IF;
3597: DELETE ap_invoices_interface
3598: WHERE invoice_num = import_rec.invoice_number
3599: AND group_id = p_group_id;
3600: l_rowcount := SQL%ROWCOUNT;
3601: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3598: WHERE invoice_num = import_rec.invoice_number
3599: AND group_id = p_group_id;
3600: l_rowcount := SQL%ROWCOUNT;
3601: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3602: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoices_interface.');
3603: END IF;
3604: EXCEPTION
3605: WHEN OTHERS THEN
3606: p_error_code := g_FAILURE;
3604: EXCEPTION
3605: WHEN OTHERS THEN
3606: p_error_code := g_FAILURE;
3607: p_error_desc := SQLERRM;
3608: l_location := l_module_name||'.delete_ap_invoices_interface';
3609: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3610: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3611: END;
3612: END IF;