| AP Invoices Interfaces Code [message #203231] |
Tue, 14 November 2006 07:04  |
Rajkumar_mj
Messages: 18
Registered: July 2006
Location: Chenna
|
Junior Member |
|
|
Hi All
I need to develop the interface program load the data from legacy system to Oracle.
Does any one have the code for AP Invoice Interface?
Thanks in advance.
Regards
Rajkumar
|
|
|
|
|
|
| Re: AP Invoices Interfaces Code [message #205062 is a reply to message #203231] |
Thu, 23 November 2006 03:09  |
abed24
Messages: 18
Registered: October 2006
Location: Jordan
|
Junior Member |
|
|
create temporary table AP_OPEN_INVOICES_TEMP and load the excel file in it.
run AP_INVOICE_INTER.main which fills AP_INVOICES_INTERFACE
then AP_INVOICE_LINE_INTER.main which fills AP_INVOICE_LINES_INTERFACE,
Then call APXIIMPT concurrent program.
the attached file shows a sample data to be loaded.
here is the packages :
PACKAGE "AP_INVOICE_INTER" AS
-- Status variable for Batch cursors
"AP_INVOICES_INTERFACE_St" BOOLEAN;
"CONST_DATE_FORMAT" VARCHAR2(10) := 'dd-mm-yyyy';
"CONST_SOURCE" VARCHAR2(20) := 'Import';
"CONST_STATUS" VARCHAR2(30) := 'NEW';
-- Procedure Main -- Entry point in package "AP_INVOICE_INTER"
PROCEDURE Main;
END "AP_INVOICE_INTER";
PACKAGE BODY "AP_INVOICE_INTER" AS
---------------------------------------------------------------------------
-- Function "AP_INVOICES_INTERFACE_Bat"
-- performs batch extraction
-- Returns TRUE on success
-- Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "AP_INVOICES_INTERFACE_Bat" RETURN BOOLEAN IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
BEGIN
INSERT
/*+ APPEND PARALLEL("AP_INVOICES_INTERFACE", DEFAULT, DEFAULT)*/
INTO
ap."AP_INVOICES_INTERFACE"
("INVOICE_ID",
"INVOICE_NUM",
"INVOICE_TYPE_LOOKUP_CODE",
"INVOICE_DATE",
"VENDOR_ID",
"VENDOR_NUM",
"VENDOR_NAME",
"VENDOR_SITE_ID",
"VENDOR_SITE_CODE",
"INVOICE_AMOUNT",
"INVOICE_CURRENCY_CODE",
"STATUS",
"SOURCE",
"PAYMENT_METHOD_LOOKUP_CODE")
(SELECT
ap."AP_INVOICES_INTERFACE_S".NEXTVAL "NEXTVAL",
"AP_OPEN_INVOICES"."INVOICE_NUM" "INVOICE_NUM",
"GET_AP_LOOKUP_VAL"("AP_OPEN_INVOICES"."INVOICE_TYPE") "Result",
TO_DATE("AP_OPEN_INVOICES"."INVOICE_DATE",(AP_INVOICE_INTER."CONST_DATE_FORMAT")) "Result$0",
"GET_VENDOR_ID"("AP_OPEN_INVOICES"."SUPPLIER_NAME") "Result$1",
"GET_VENDOR_NUMBER"("AP_OPEN_INVOICES"."SUPPLIER_NAME") "Result$2",
"AP_OPEN_INVOICES"."SUPPLIER_NAME" "SUPPLIER_NAME",
"GET_VENDOR_SITE_ID"(("GET_VENDOR_ID"("AP_OPEN_INVOICES"."SUPPLIER_NAME")),"AP_OPEN_INVOICES"."SUPPLIER_SITE_NAME") "Result$3",
"AP_OPEN_INVOICES"."SUPPLIER_SITE_NAME" "SUPPLIER_SITE_NAME",
"AP_OPEN_INVOICES"."INVOICE_AMOUNT" "INVOICE_AMOUNT",
"AP_OPEN_INVOICES"."CURRENCY_CODE" "CURRENCY_CODE",
AP_INVOICE_INTER."CONST_STATUS" "STATUS",
AP_INVOICE_INTER."CONST_SOURCE" "SOURCE",
"INVOICE_PAYMENT_METHOD"("AP_OPEN_INVOICES"."PAYMENT_METHOD") "Result$4"
FROM
"AP_OPEN_INVOICES_TEMP" "AP_OPEN_INVOICES"
);
COMMIT;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('sql = '||sqlerrm);
ROLLBACK;
COMMIT;
RETURN FALSE;
END;
COMMIT;
RETURN TRUE;
END "AP_INVOICES_INTERFACE_Bat" ;
PROCEDURE Main IS
get_batch_status BOOLEAN := TRUE;
BEGIN
-- Initialize all batch status variables
"AP_INVOICES_INTERFACE_St" := FALSE;
"AP_INVOICES_INTERFACE_St" := "AP_INVOICES_INTERFACE_Bat";
END Main;
END "AP_INVOICE_INTER";
PACKAGE "AP_INVOICE_LINE_INTER" AS
-- Status variable for Batch cursors
"AP_INVOICE_LINES_INTERFACE_St" BOOLEAN;
"CONST_ITEM_TYPE" VARCHAR2(100) := 'INVOICE DISTRIBUTION TYPE';
-- Procedure Main -- Entry point in package "AP_INVOICE_LINE_INTER"
PROCEDURE Main;
END "AP_INVOICE_LINE_INTER";
PACKAGE BODY "AP_INVOICE_LINE_INTER" AS
---------------------------------------------------------------------------
-- Function "AP_INVOICE_LINES_INTERFACE_Bat"
-- performs batch extraction
-- Returns TRUE on success
-- Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "AP_INVOICE_LINES_INTERFACE_Bat" RETURN BOOLEAN IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
BEGIN
INSERT
/*+ APPEND PARALLEL("AP_INVOICE_LINES_INTERFACE", DEFAULT, DEFAULT)*/
INTO
ap."AP_INVOICE_LINES_INTERFACE"
("INVOICE_ID",
"INVOICE_LINE_ID",
"LINE_NUMBER",
"LINE_TYPE_LOOKUP_CODE",
"AMOUNT",
"DIST_CODE_COMBINATION_ID")
(SELECT
"GET_INVOICE_ID"("AP_OPEN_INVOICES_TEMP"."INVOICE_NUM") "Result",
ap."AP_INVOICE_LINES_INTERFACE_S".NEXTVAL "NEXTVAL",
"AP_OPEN_INVOICES_TEMP"."LINE_NO" "LINE_NO",
"GET_AP_LOOKUP_CODES"((AP_INVOICE_LINE_INTER."CONST_ITEM_TYPE"),"AP_OPEN_INVOICES_TEMP"."LINE_TYPE") "Result$0",
"AP_OPEN_INVOICES_TEMP"."AMOUNT" "AMOUNT",
"GET_SEGMENTS"("AP_OPEN_INVOICES_TEMP"."LIABILITY_ACCOUNT") "Result$1"
FROM
"AP_OPEN_INVOICES_TEMP" "AP_OPEN_INVOICES_TEMP"
);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
COMMIT;
RETURN FALSE;
END;
COMMIT;
RETURN TRUE;
END "AP_INVOICE_LINES_INTERFACE_Bat" ;
PROCEDURE Main IS
get_batch_status BOOLEAN := TRUE;
BEGIN
-- Initialize all batch status variables
"AP_INVOICE_LINES_INTERFACE_St" := FALSE;
"AP_INVOICE_LINES_INTERFACE_St" := "AP_INVOICE_LINES_INTERFACE_Bat";
END Main;
END "AP_INVOICE_LINE_INTER";
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
