PROCEDURE create_invoices_prc (p_trx_number IN VARCHAR2, p_rowid IN VARCHAR2) IS l_return_status VARCHAR2 (1); l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type; l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type; l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type; l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type; l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type; v_description VARCHAR2 (300); v_line_type VARCHAR2 (300); l_msg_data VARCHAR2 (2000); v_uom_code VARCHAR2 (50); v_batch_source_name VARCHAR2 (50); v_cust_trx_type_name VARCHAR2 (20); v_currency_code VARCHAR2 (15); vorig_system_bill_customer_ref VARCHAR2 (240); vorig_system_bill_address_ref VARCHAR2 (240); v_trx_number VARCHAR2 (20); v_rowid VARCHAR2 (100); v_error_buffer VARCHAR2 (4000); v_status_flag VARCHAR2 (4); v_trx_date DATE; x_msg_count NUMBER; x_cust_account_id NUMBER; x_site_use_id NUMBER; i NUMBER; l_cust_trx_id NUMBER; v_resp_id NUMBER; v_tax_rate NUMBER; v_memo_line_id NUMBER; v_code_combination_id NUMBER; v_trx_dist_id NUMBER; v_trx_line_id NUMBER; v_cust_trx_id NUMBER; l_msg_count NUMBER; v_trx_header_id NUMBER; v_item_id NUMBER; v_attribute12 VARCHAR2 (240); v_org_id NUMBER; v_invoice_number VARCHAR2 (50); v_created_invoice VARCHAR2(20); v_customer_trx_id NUMBER; v_site_number NUMBER; x_inv_id NUMBER; lx_customer_trx_id NUMBER; v_ord_id NUMBER; v_tax_code NUMBER; v_offer_id VARCHAR2 (100); v_count NUMBER := 0; v_item VARCHAR2 (200); v_taxable NUMBER; v_batch_source_id NUMBER; v_term_id NUMBER; v_term_name VARCHAR2(15); v_nb_erreur NUMBER; /* Parcours des entetes factures (headers) */ CURSOR lheader IS SELECT batch_source_name, cust_trx_type_name, currency_code, trx_date, orig_system_bill_customer_ref, orig_system_bill_address_ref, trx_number, ROWID, tax_rate, interface_source_system, telephone_number, oa_client, date_echeance FROM xxnes_invoice_header_isol_tbl WHERE trx_number = p_trx_number AND ROWID = p_rowid; /* Recuperations des infos correspondantes ¿a facture du header factures en cours de traitement*/ CURSOR linvoice (v_trx_number IN VARCHAR, v_org_id VARCHAR2) IS SELECT DISTINCT product, quantity, unit_selling_price, amount, trx_number, attribute1, attribute2, segment1 FROM xxnes_invoice_lines_isol_tbl WHERE trx_number = v_trx_number ; -- AND interface_source_system = v_org_id; -- Cursor to get the error message if any CURSOR error_cursor IS SELECT error_message FROM ar_trx_errors_gt; BEGIN BEGIN BEGIN SELECT batch_source_id INTO v_batch_source_id FROM ra_batch_sources_all WHERE name = 'NESSICO' ; EXCEPTION WHEN NO_DATA_FOUND THEN v_batch_source_id := 1309; END; FOR hcur IN lheader LOOP v_org_id := 86; -- fnd_client_info.set_org_context (86); /* v_org_id := 86; fnd_global.apps_initialize (0, 50549, 600); mo_global.set_policy_context ('S', 86); mo_global.init ('AR');*/ IF (( TRIM (hcur.interface_source_system) = 'OSN' OR TRIM (hcur.interface_source_system) = 'SN' OR TRIM (hcur.interface_source_system) = 'SONATEL' ) ) THEN --fnd_client_info.set_org_context (86); v_org_id := 86; fnd_global.apps_initialize (150255, 50549, 600); mo_global.set_policy_context ('S', 86); mo_global.init ('AR'); ---controle d'existance de la facture dans ra_cust_trx_all -- BEGIN -- SELECT COUNT (*) -- INTO v_count -- FROM ra_customer_trx_all -- WHERE trx_number = hcur.trx_number AND org_id = 86; -- EXCEPTION -- WHEN NO_DATA_FOUND -- THEN -- v_count := 0; -- WHEN TOO_MANY_ROWS -- THEN -- v_count := 1; -- END; --- On ne doit traiter que les nouvelles factures (eviter les doublons factures) -- IF v_count = 0 -- THEN -- IF hcur.tax_rate <> 0 -- THEN -- v_tax_code := 1021; -- ELSE -- v_tax_code := 1007; -- END IF; -- fnd_global.apps_initialize (1064, 50549, 600); IF hcur.cust_trx_type_name LIKE 'PERIODIQUE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1155; ELSIF hcur.cust_trx_type_name LIKE 'IMMEDIATE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1156; ELSIF hcur.cust_trx_type_name LIKE 'ISOLE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1153; END IF; l_batch_source_rec.batch_source_id := v_batch_source_id; --HIGHDEAL ELSIF TRIM (hcur.interface_source_system) = 'OML' THEN fnd_client_info.set_org_context (84); v_org_id := 84; IF hcur.tax_rate <> 0 THEN v_tax_code := 1023; ELSE v_tax_code := 1003; END IF; fnd_global.apps_initialize (1064, 50551, 600); -- 107096 IF hcur.cust_trx_type_name LIKE 'PERIODIQUE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1157; ELSIF hcur.cust_trx_type_name LIKE 'IMMEDIATE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1159; ELSIF hcur.cust_trx_type_name LIKE 'ISOLE%' THEN l_trx_header_tbl (1).cust_trx_type_id := 1158; END IF; l_batch_source_rec.batch_source_id := v_batch_source_id; END IF; IF TRIM (hcur.orig_system_bill_customer_ref) IS NOT NULL THEN -- Get the Customer Account id and assign it to the bill to field. /*xx_pos_ar_utilities_pkg.pos_get_cust_account_id (hcur.orig_system_bill_customer_ref, hcur.orig_system_bill_address_ref, x_cust_account_id );*/ /* get_customer_infos('Mobile-' || hcur.orig_system_bill_address_ref, x_site_use_id, x_cust_account_id ); */ get_cust_infos_from_codecompte(hcur.orig_system_bill_address_ref, x_site_use_id, x_cust_account_id ); -- PROCEDURE pour trouver le site_use_id --GET_SITE_USE_ID (hcur.telephone_number, x_cust_account_id, x_site_use_id); -- batch_source_rec.batch_source_id and l_trx_header_tbl (1).trx_header_id -- should depend on the source system SELECT xx_invoices_seq.NEXTVAL INTO v_trx_header_id FROM DUAL; -- BEGIN -- SELECT order_header_id -- INTO v_ord_id -- FROM pos_schema.pos_order_header -- WHERE invoice_number = p_trx_number -- AND org_id = -- DECODE (hcur.interface_source_system, -- 'OSN', '86', -- 'OML', '84', -- 'SONATEL', '86', -- hcur.interface_source_system -- ); -- -- AND order_type IN (1263, 1296); -- EXCEPTION -- WHEN NO_DATA_FOUND -- THEN -- v_ord_id := -1; -- WHEN TOO_MANY_ROWS -- THEN -- SELECT order_header_id -- INTO v_ord_id -- FROM pos_schema.pos_order_header -- WHERE invoice_number = p_trx_number -- AND org_id = -- DECODE (hcur.interface_source_system, -- 'OSN', '86', -- 'OML', '84', -- 'SONATEL', '86', -- hcur.interface_source_system -- ) -- -- AND order_type IN (1263, 1296) -- AND ROWNUM = 1; -- END; -- -- BEGIN -- SELECT line_name_id -- INTO v_offer_id -- FROM pos_schema.pos_order_lines -- WHERE order_header_id = v_ord_id -- AND line_obj_type = 'OFFER'; -- EXCEPTION -- WHEN NO_DATA_FOUND -- THEN -- v_offer_id := '-1'; -- END; -- IF v_offer_id <> 'OCS_ALIZE' and v_produ <> 'CS_ALIZ_ABONNEMENT' -- THEN IF hcur.trx_number like 'VDECH%' THEN v_term_name := TO_CHAR((TRUNC(hcur.date_echeance ) - TRUNC(hcur.trx_date))) ; dbms_output.put_line('Dans le cas d''une facture Echelonne ' || hcur.trx_number); dbms_output.put_line('valeur du v_term_name = ' || v_term_name); BEGIN select term_id into v_term_id from ra_terms_tl where upper(name) = v_term_name || ' NET' and language = 'F' ; EXCEPTION WHEN NO_DATA_FOUND THEN v_term_id := -1 ; END; ELSE v_term_id := 4; END IF; -- test sur la valeur de l'echeance IF v_term_id != -1 THEN UPDATE xxnes_invoice_header_isol_tbl set RETURNED_ADJ_ID = v_term_id where trx_number = hcur.trx_number and rowid = hcur.rowid; commit; l_trx_header_tbl (1).trx_header_id := v_trx_header_id; l_trx_header_tbl (1).trx_date := hcur.trx_date; l_trx_header_tbl (1).gl_date := TRUNC (SYSDATE); -- to_date('30/10/2014','dd/mm/yyyy'); -- l_trx_header_tbl (1).bill_to_site_use_id := x_site_use_id; if length(hcur.trx_number) <= 20 then l_trx_header_tbl (1).trx_number := hcur.trx_number; end if; l_trx_header_tbl (1).trx_currency := hcur.currency_code; l_trx_header_tbl (1).bill_to_customer_id := x_cust_account_id; l_trx_header_tbl (1).attribute1 := hcur.telephone_number; l_trx_header_tbl (1).term_id := v_term_id; l_trx_header_tbl (1).finance_charges := 'N'; l_trx_header_tbl (1).status_trx := 'OP'; l_trx_header_tbl (1).printing_option := 'NOT'; l_trx_header_tbl (1).attribute10 := v_ord_id; l_trx_header_tbl (1).finance_charges := 'N'; l_trx_header_tbl (1).interface_header_attribute1 := hcur.oa_client; i := 1; /* parcours des lignes factures de chaque entete facutre (header) ) */ FOR icur IN linvoice (hcur.trx_number, hcur.interface_source_system ) LOOP icur.product := TRIM (icur.product); xx_pos_ar_utilities_pkg.pos_get_inv_item_id (icur.product, v_org_id, x_inv_id ); /** Mois@ Evol Newton : Complément numéraire**/ IF (icur.product = 'PAP_COMP_NUM') THEN SELECT description INTO v_item FROM mtl_system_items_b WHERE attribute2 = 'PAP_COMP_NUM' AND organization_id = 85; v_item := v_item || '-' || icur.attribute1; l_trx_lines_tbl (i).description := v_item; END IF; IF x_inv_id = -1 AND icur.segment1 is not null THEN BEGIN SELECT inventory_item_id INTO x_inv_id FROM mtl_system_items_b WHERE segment1 = icur.segment1 AND organization_id = 86 ; dbms_output.put_line('valeur du segment ' || icur.segment1); dbms_output.put_line('Inventory_item_id recupere à partir du segment = ' || x_inv_id); EXCEPTION WHEN NO_DATA_FOUND THEN x_inv_id := -1; END; END IF; -- Recherche si article taxable BEGIN select 1 into v_taxable from mtl_system_items_b where organization_id = 86 and inventory_item_id = x_inv_id and tax_code is not null and tax_code not in ('EXO', 'TVA_0%'); EXCEPTION WHEN NO_DATA_FOUND THEN v_taxable := 0; END; -- dbms_output.put_line(' affectation des infos de ligne pour la facture '); dbms_output.put_line('Numero de la ligne : ' || i || ' Pour la facture ' || hcur.trx_number); dbms_output.put_line(' les infos de la ligne cette facture '); dbms_output.put_line('x_inv_id : ' || x_inv_id || ' produit ' || icur.product || ' unit_selling_price ' || icur.unit_selling_price ); l_trx_lines_tbl (i).trx_header_id := v_trx_header_id; l_trx_lines_tbl (i).trx_line_id := i; l_trx_lines_tbl (i).line_number := i; l_trx_lines_tbl (i).inventory_item_id := x_inv_id; l_trx_lines_tbl (i).description := icur.product; --AJOUT ABDOUKARIM l_trx_lines_tbl (i).quantity_invoiced := icur.quantity; -- 1; l_trx_lines_tbl (i).attribute4 := icur.quantity; l_trx_lines_tbl (i).unit_selling_price := icur.unit_selling_price; -- icur.amount; l_trx_lines_tbl (i).line_type := 'LINE'; --l_trx_lines_tbl (i).vat_tax_id := v_tax_code; l_trx_lines_tbl (i).tax_exempt_flag := 'S'; i := i + 1; END LOOP; /* UPDATE xxnes_invoice_header_isol_tbl set TOTAL_APPLIED_AMOUNT = -1 where trx_number = hcur.trx_number and rowid = hcur.rowid; commit;*/ dbms_output.put_line('appel API pour creation facture ' || hcur.trx_number ); ar_invoice_api_pub.create_single_invoice (p_api_version => 1.0, p_init_msg_list => 'T', p_commit => 'T', p_batch_source_rec => l_batch_source_rec, p_trx_header_tbl => l_trx_header_tbl, p_trx_lines_tbl => l_trx_lines_tbl, p_trx_dist_tbl => l_trx_dist_tbl, p_trx_salescredits_tbl => l_trx_salescredits_tbl, x_customer_trx_id => lx_customer_trx_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data ); i := 1; dbms_output.put_line(' fin appel API pour ' || hcur.trx_number ); /* UPDATE xxnes_invoice_header_isol_tbl set TOTAL_APPLIED_AMOUNT = 10 where trx_number = hcur.trx_number and rowid = hcur.rowid; */ commit; -- ======================================= /* FOR err_cur IN error_cursor LOOP v_error_buffer := v_error_buffer || '(' || i || ')' || err_cur.error_message || CHR (10); END LOOP; */ -- ========================================= dbms_output.put_line('return_status from create single invoice ' || l_return_status ); dbms_output.put_line('l_msg_count from create single invoice ' || l_msg_count ); /* IF l_return_status <> apps.fnd_api.g_ret_sts_success THEN v_error_buffer := v_error_buffer || apps.fnd_msg_pub.get (p_msg_index => apps.fnd_msg_pub.g_last, p_encoded => apps.fnd_api.g_false ); END IF; */ BEGIN SELECT COUNT (*) INTO v_count FROM ar_payment_schedules_all WHERE trx_number = hcur.trx_number; EXCEPTION WHEN NO_DATA_FOUND THEN v_count := 0; END; IF v_count>0 THEN v_status_flag := 'S'; v_error_buffer:=null; ELSE v_status_flag := 'E'; v_nb_erreur := 0; FOR err_cur IN error_cursor LOOP v_nb_erreur := v_nb_erreur + 1; v_error_buffer := -- v_error_buffer '(' || i || ')' || err_cur.error_message || CHR (10); /* v_error_buffer := v_error_buffer || '(' || i || ')' || err_cur.error_message || CHR (10); */ dbms_output.put_line(' contenu de v_error_buffer ' || v_error_buffer); dbms_output.put_line(' valeur de v_nb_erreur ' || v_nb_erreur); END LOOP; END IF; -- delete from ar_trx_errors_gt; -- execute immediate ('truncate table ar_trx_errors_gt'); DBMS_OUTPUT.PUT_LINE('message d''erreur ' || v_error_buffer); execute immediate ('delete from ar_trx_errors_gt'); COMMIT; /* BEGIN SELECT trx_number INTO v_created_invoice FROM ra_customer_trx_all WHERE customer_trx_id = lx_customer_trx_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_created_invoice := NULL; lx_customer_trx_id := NULL; END; IF v_created_invoice IS NULL THEN v_status_flag := 'E'; ELSE v_status_flag := 'S'; END IF; */ /* IF x_return_status != apps.fnd_api.g_ret_sts_success THEN v_error_buffer := v_error_buffer || apps.fnd_msg_pub.get (p_msg_index => apps.fnd_msg_pub.g_last, p_encoded => apps.fnd_api.g_false ); END IF;*/ /* IF l_msg_count > 0 -- l_return_status != apps.fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.PUT_LINE('On est dans le if l_msg_count ' || l_msg_count); FOR i IN 1 .. l_msg_count LOOP v_error_buffer := v_error_buffer || l_msg_data || ( i || '. ' || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255 ) ); END LOOP; END IF; */ /* IF LENGTH (v_error_buffer) > 1 OR l_msg_data IS NOT NULL THEN v_status_flag := 'E'; ELSE v_status_flag := 'S'; END IF; */ if length(hcur.trx_number) <= 20 then v_trx_number := hcur.trx_number; end if; /* Mise ¿our de la table XXCRMO.XX_INVOICE_HEADERS_TBL */ UPDATE xxnes_invoice_header_isol_tbl SET interface_creation_date = SYSDATE, interface_status = v_status_flag, interface_error_message = v_error_buffer, --NVL (v_error_buffer, l_msg_data) --|| x_cust_account_id, returned_cust_trx_id = lx_customer_trx_id, returned_cust_acct_id = x_cust_account_id, returned_site_use_id = x_site_use_id WHERE trx_number = hcur.trx_number -- AND ROWID = hcur.ROWID; ; -- END IF; ELSE UPDATE xxnes_invoice_header_isol_tbl SET interface_creation_date = NULL, interface_status = 'E', interface_error_message = 'INVALID TERM_ID' WHERE trx_number = hcur.trx_number AND ROWID = hcur.ROWID; END IF; ELSE UPDATE xxnes_invoice_header_isol_tbl SET interface_creation_date = NULL, interface_status = 'E', interface_error_message = 'CUSTOMER ID IS NULL' WHERE trx_number = hcur.trx_number AND ROWID = hcur.ROWID; END IF; v_status_flag := NULL; v_error_buffer := ''; l_return_status := NULL; l_msg_count := NULL; l_msg_data := NULL; -- END IF; --- Fin test existence facture dans Oracle END LOOP; v_tax_code := NULL; END; END;