Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE error 6550 in FDPSTP
| ORACLE error 6550 in FDPSTP [message #543619] |
Wed, 15 February 2012 23:06  |
 |
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
|
Junior Member |
|
|
Hi,
I am creating a package. In case my package, i am want to run pararel all procedure (FillAndSend_IM1,FillAndSend_IM2,FillAndSend_IM3,FillAndSend_IM4 ) .
in package i using dbms_job.submit,
dbms_job.submit( l_job, 'FillAndSend_IM1;' );
dbms_job.submit( l_job, 'FillAndSend_IM2;' );
dbms_job.submit( l_job, 'FillAndSend_IM3;' );
dbms_job.submit( l_job, 'FillAndSend_IM4;' );
when procedure is runing , found error
"ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 93:
PLS-00201: identifier 'FILLANDSEND_IM1' must be declared"
How and where i must be declared ?
Thank you,
gvE5
|
|
|
|
|
|
| Re: ORACLE error 6550 in FDPSTP [message #543798 is a reply to message #543630] |
Thu, 16 February 2012 21:51  |
|
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
|
Junior Member |
|
|
DB 9.2.0.6
OS SUN
I want Job running in procedure, and I have attached a sample procedure.
Procedure fillandsend_im2; fillandsend_im3 and fillandsend_im4 not copy because same as fillandsend_im1 different in parameter where " AND SUBSTR (Gr_store, 3, 3) BETWEEN '100' AND '140'" => im2 SUBSTR (Gr_store, 3, 3) between '141' and '170' ,im3 SUBSTR (Gr_store, 3, 3) between '111' and '200', im4 SUBSTR (Gr_store, 3, 3) between '201' and '300',
record by site := 100 record.
I hope the process can be faster, if using a parallel process, and now the process is running 4 hours. start 7.00 finish 11.00.
Sorry if code not good, because i am beginner 
Thanks
GVE5
|
|
|
|
|
|
|
|
| Re: ORACLE error 6550 in FDPSTP [message #543834 is a reply to message #543806] |
Fri, 17 February 2012 02:58 |
|
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
|
Junior Member |
|
|
When you are running the procedure which contains this code or when the job is running?
---- When I running the procedure which contains this code
execute ap_im_int_pkg.ap_im_int_start;
found error :
ORA-06550: line 1, column 93:
PLS-00201: identifier 'FILLANDSEND_IM1' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
Sorry , text can't inline and formatted, i has been create in notepad and result inline and formatted. when i copy & paste in Posting form body .( justification is left ) .
thanks
GvE5
CREATE OR REPLACE PACKAGE ap_im_int_pkg
AS
PROCEDURE ap_im_int_start;
PROCEDURE gr2ora_im;
PROCEDURE fillandsend_im1;
PROCEDURE temp2orawksh (
vinvoice_id ap_invoices_interface.invoice_id%TYPE,
vinvoice_num ap_invoices_interface.invoice_num%TYPE,
vinvoice_type_lookup_code ap_invoices_interface.invoice_type_lookup_code%TYPE,
vinvoice_date ap_invoices_interface.invoice_date%TYPE,
vvendor_num ap_invoices_interface.vendor_num%TYPE,
vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE,
vinvoice_amount ap_invoices_interface.invoice_amount%TYPE,
vinvoice_currency_code ap_invoices_interface.invoice_currency_code%TYPE,
vterms_id ap_invoices_interface.terms_id%TYPE,
vterms_name ap_invoices_interface.terms_name%TYPE,
vdescription ap_invoices_interface.description%TYPE,
vpay_group_lookup_code ap_invoices_interface.pay_group_lookup_code%TYPE,
vgl_date ap_invoices_interface.gl_date%TYPE,
vterms_date ap_invoices_interface.terms_date%TYPE,
vattribute2 ap_invoices_interface.attribute2%TYPE,
vorg_id ap_invoices_interface.org_id%TYPE,
vattribute3 VARCHAR2,
vattribute4 VARCHAR2
);
PROCEDURE temp2orawksd (
vinvoice_id ap_invoice_lines_interface.invoice_id%TYPE,
vinvoice_line_id ap_invoice_lines_interface.invoice_line_id%TYPE,
vamount ap_invoice_lines_interface.amount%TYPE,
vaccounting_date ap_invoice_lines_interface.accounting_date%TYPE,
vdescription ap_invoice_lines_interface.description%TYPE,
vdist_code_concatenated ap_invoice_lines_interface.dist_code_concatenated%TYPE,
vattribute1 ap_invoice_lines_interface.attribute1%TYPE,
vorg_id ap_invoice_lines_interface.org_id%TYPE,
vattribute3 VARCHAR2,
vattribute4 VARCHAR2
);
PROCEDURE deltempflag_im;
FUNCTION checkdescr (descr IN VARCHAR2, inv_amount IN NUMBER)
RETURN NUMBER;
PROCEDURE createcancelim (descr IN VARCHAR2);
FUNCTION checkinvnumb (v_invoice IN VARCHAR2)
RETURN NUMBER;
END ap_im_int_pkg;
/
CREATE OR REPLACE PACKAGE BODY ap_im_int_pkg
AS
PROCEDURE ap_im_int_start
IS
l_job NUMBER;
BEGIN
-- insert into temporary table from GrSystem
gr2ora_im;
-- testing : 1 job to running
BEGIN
DBMS_JOB.submit (l_job, 'FillAndSend_IM1;');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
-- 3. delete all record if flag = 'P' in Temporary Table
deltempflag_im;
-- 4. Commit
COMMIT;
END;
-- *********************************************
PROCEDURE gr2ora_im
IS
BEGIN
INSERT INTO ap_im_int_ora
(im_interface_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_num, vendor_site_code, amount,
invoice_currency_code, terms_name, description,
pay_group_lookup_code, gl_date, terms_date, gr_store,
accounting_date, type_code, sales_chanel, division,
inventory, dept)
SELECT im_interface_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_num, vendor_site_code, amount,
invoice_currency_code, terms_name, description,
pay_group_lookup_code, gl_date, terms_date, gr_store,
accounting_date, type_code, sales_chanel, division,
inventory, DECODE (sales_chanel, '6', 'CAFETARIA', dept)
FROM ap_im_int@link2prd.com
ORDER BY invoice_num, type_code;
IF TRUNC (SYSDATE) <= TO_DATE ('01/04/2011', 'dd/mm/yyyy')
THEN
UPDATE ap_im_int_ora
SET gl_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy'),
accounting_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy')
WHERE flag IS NULL AND SUBSTR (gr_store, 3, 3) = '227';
END IF;
UPDATE ap_im_int_ora
SET gl_date = TRUNC (SYSDATE - 1),
accounting_date = TRUNC (SYSDATE - 1)
WHERE TO_CHAR (gl_date, 'yyyymm') < TO_CHAR (TRUNC (SYSDATE - 6), 'yyyymm')
AND flag IS NULL;
END;
-- *********************************************
PROCEDURE fillandsend_im1
IS
CURSOR gr_data
IS
SELECT *
FROM ap_im_int_ora
WHERE flag IS NULL
AND SUBSTR (gr_store, 3, 3) IN (
SELECT store_code
FROM parameter_ora
WHERE module = 'MATC'
AND status = 1)
AND amount <> 0
AND type_code = '8'
AND SUBSTR (gr_store, 3, 3) BETWEEN '100' AND '140'
ORDER BY SUBSTR (gr_store, 3, 3),
vendor_num,
vendor_site_code,
invoice_num;
rgr gr_data%ROWTYPE;
nno NUMBER;
cnewseq ap_invoices_interface.invoice_id%TYPE;
ctermsid ap_invoices_interface.terms_id%TYPE;
cvendor_site_code rgr.vendor_site_code%TYPE;
cvendor_num rgr.vendor_num%TYPE;
cinvoice_num rgr.invoice_num%TYPE;
cgrstored rgr.gr_store%TYPE;
cinv_num rgr.invoice_num%TYPE;
ccancel CHAR (1);
vreturn NUMBER;
vstorenew VARCHAR2 (3);
vsubstore VARCHAR2 (3);
vtoko_tampil VARCHAR2 (10) := 1;
dummy VARCHAR2 (100);
vattribute4 VARCHAR2 (3);
lcreate BOOLEAN := TRUE;
BEGIN
cvendor_num := 'X';
cvendor_site_code := 'X';
cinvoice_num := 'X';
OPEN gr_data;
LOOP
FETCH gr_data INTO rgr;
EXIT WHEN gr_data%NOTFOUND;
vreturn := 1;
dummy := NULL;
ctermsid := supp_int_pkg.findtermid (rgr.terms_name);
lcreate := TRUE;
IF SUBSTR (rgr.invoice_num, INSTR (rgr.invoice_num, 'CL', 1), 2) = 'CL' AND ctermsid <> 0
THEN
vreturn := checkinvnumb (SUBSTR (rgr.invoice_num, 1, 13));
ctermsid := vreturn;
END IF;
IF ctermsid <> 0
THEN
ctermsid := supp_int_pkg.findtermid (rgr.terms_name);
ccancel := 'N';
IF checkdescr (rgr.description, ROUND (rgr.amount, 2)) = 1
THEN
createcancelim (rgr.description);
ccancel := 'Y';
ELSE
IF SUBSTR (rgr.invoice_num, -1) = 'R'
THEN
BEGIN
SELECT invoice_num
INTO dummy
FROM ap_invoices_all
WHERE description = rgr.description
AND invoice_type_lookup_code = 'STANDARD'
AND invoice_amount = ROUND (rgr.amount, 2)
AND invoice_num =
SUBSTR (rgr.invoice_num,
1,
LENGTH (rgr.invoice_num) - 1
)
AND ROWNUM = 1
ORDER BY creation_date DESC, invoice_id DESC;
createcancelim (rgr.description);
ccancel := 'Y';
lcreate := TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lcreate := TRUE;
END;
ELSE
BEGIN
SELECT 1
INTO dummy
FROM ap_invoices_all
WHERE description = rgr.description
AND invoice_type_lookup_code = 'STANDARD'
AND invoice_amount = ROUND (rgr.amount, 2)
AND invoice_num = rgr.invoice_num
AND ROWNUM = 1
ORDER BY creation_date DESC, invoice_id DESC;
lcreate := FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
SELECT invoice_num
INTO dummy
FROM ap_invoices_all
WHERE description = rgr.description
AND invoice_type_lookup_code = 'STANDARD'
AND invoice_amount = ROUND (rgr.amount, 2)
AND ROWNUM = 1
ORDER BY creation_date DESC, invoice_id DESC;
createcancelim (rgr.description);
ccancel := 'Y';
lcreate := TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lcreate := TRUE;
END;
END;
END IF;
END IF;
cinv_num := rgr.invoice_num;
cnewseq := TO_NUMBER ( TO_CHAR (SYSTIMESTAMP, 'yymm')|| TO_CHAR (supp_int_pkg.getnewseq ()));
IF ( SUBSTR (rgr.gr_store, 3, 3) = '100'
OR SUBSTR (rgr.gr_store, 3, 3) = '199'
)
AND rgr.sales_chanel = 3
THEN
vstorenew := '201';
vsubstore := '201';
ELSE
IF supp_int_pkg.func_grbrancmart (SUBSTR (rgr.gr_store, 3, 3)) = '201'
THEN
vstorenew := '201';
vsubstore := '201';
ELSE
vstorenew := SUBSTR (rgr.gr_store, 3, 3);
IF SUBSTR (rgr.gr_store, 3, 3) IN ('199', '100')
THEN
vsubstore := '101';
ELSE
vsubstore := SUBSTR (rgr.gr_store, 3, 3);
END IF;
END IF;
END IF;
vattribute4 := vsubstore;
IF lcreate
THEN
IF SUBSTR (rgr.vendor_site_code, 1, 1) <> 'C'
AND (rgr.vendor_num <> cvendor_num)
OR (rgr.vendor_site_code <> cvendor_site_code)
OR (rgr.invoice_num <> cinvoice_num)
THEN
temp2orawksh (cnewseq,
cinv_num,
rgr.invoice_type_lookup_code,
rgr.invoice_date,
rgr.vendor_num,
rgr.vendor_site_code,
ROUND (rgr.amount, 2),
rgr.invoice_currency_code,
ctermsid,
rgr.terms_name,
rgr.description,
rgr.pay_group_lookup_code,
rgr.gl_date,
rgr.terms_date,
rgr.dept,
supp_int_pkg.findorg_id (vstorenew),
rgr.inventory,
vattribute4
);
cvendor_num := rgr.vendor_num;
cvendor_site_code := rgr.vendor_site_code;
cinvoice_num := rgr.invoice_num;
END IF;
END IF;
nno := TO_CHAR (supp_int_pkg.getnewseq ());
IF lcreate
THEN -
IF vsubstore IN ('100', '199')
THEN
vsubstore := '101';
END IF;
temp2orawksd (cnewseq,
nno,
ROUND (rgr.amount, 2),
rgr.accounting_date,
rgr.description,
supp_int_pkg.func_griyamart (vstorenew)
|| '.000.9922001.000.'
|| vsubstore
|| '.00',
rgr.dept,
supp_int_pkg.findorg_id (vstorenew),
rgr.inventory,
vattribute4
);
UPDATE ap_im_int_ora
SET flag = 'P'
WHERE im_interface_id = rgr.im_interface_id
RETURNING invoice_num, gr_store
INTO cinvoicenum, cgrstored;
UPDATE ap_im_int_ora
SET flag = 'P'
WHERE invoice_num = cinvoicenum
AND gr_store = cgrstored
AND flag IS NULL;
END IF;
END IF;
IF SUBSTR (rgr.gr_store, 3, 3) <> vsite_display
vsite_display := SUBSTR (rgr.gr_store, 3, 3);
END IF;
END LOOP;
END;
-- *********************************************
PROCEDURE temp2orawksh (
vinvoice_id ap_invoices_interface.invoice_id%TYPE,
vinvoice_num ap_invoices_interface.invoice_num%TYPE,
vinvoice_type_lookup_code ap_invoices_interface.invoice_type_lookup_code%TYPE,
vinvoice_date ap_invoices_interface.invoice_date%TYPE,
vvendor_num ap_invoices_interface.vendor_num%TYPE,
vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE,
vinvoice_amount ap_invoices_interface.invoice_amount%TYPE,
vinvoice_currency_code ap_invoices_interface.invoice_currency_code%TYPE,
vterms_id ap_invoices_interface.terms_id%TYPE,
vterms_name ap_invoices_interface.terms_name%TYPE,
vdescription ap_invoices_interface.description%TYPE,
vpay_group_lookup_code ap_invoices_interface.pay_group_lookup_code%TYPE,
vgl_date ap_invoices_interface.gl_date%TYPE,
vterms_date ap_invoices_interface.terms_date%TYPE,
vattribute2 ap_invoices_interface.attribute2%TYPE,
vorg_id ap_invoices_interface.org_id%TYPE,
vattribute3 VARCHAR2,
vattribute4 VARCHAR2
)
IS
BEGIN
INSERT INTO ap_invoices_interface
(invoice_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_num, vendor_site_code,
invoice_amount, invoice_currency_code, terms_id,
terms_name, description, creation_date, created_by,
SOURCE, doc_category_code, payment_method_lookup_code,
pay_group_lookup_code, gl_date, terms_date,
attribute2, org_id, attribute3, attribute4
)
VALUES (vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
vinvoice_date, vvendor_num, vvendor_site_code,
vinvoice_amount, vinvoice_currency_code, vterms_id,
vterms_name, vdescription, SYSDATE, '1091',
'G.O.L.D', 'STD INV', 'CHECK',
vpay_group_lookup_code, vgl_date, vterms_date,
vattribute2, vorg_id, vattribute3, vattribute4
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
DBMS_OUTPUT.put_line ('ERROR : Invoice ID : ' || vinvoice_id);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
-- *********************************************\
PROCEDURE temp2orawksd (
vinvoice_id ap_invoice_lines_interface.invoice_id%TYPE,
vinvoice_line_id ap_invoice_lines_interface.invoice_line_id%TYPE,
vamount ap_invoice_lines_interface.amount%TYPE,
vaccounting_date ap_invoice_lines_interface.accounting_date%TYPE,
vdescription ap_invoice_lines_interface.description%TYPE,
vdist_code_concatenated ap_invoice_lines_interface.dist_code_concatenated%TYPE,
vattribute1 ap_invoice_lines_interface.attribute1%TYPE,
vorg_id ap_invoice_lines_interface.org_id%TYPE,
vattribute3 VARCHAR2,
vattribute4 VARCHAR2
)
IS
BEGIN
INSERT INTO ap_invoice_lines_interface
(invoice_id, invoice_line_id, line_number,
line_type_lookup_code, amount, accounting_date,
description, dist_code_concatenated, created_by,
creation_date, attribute1, org_id, attribute3, attribute4
)
VALUES (vinvoice_id, vinvoice_line_id, 1,
'ITEM', vamount, vaccounting_date,
vdescription, vdist_code_concatenated, '1091',
SYSDATE, vattribute1, vorg_id, vattribute3, vattribute4
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
DBMS_OUTPUT.put_line ('ERROR : Invoice ID : ' || vinvoice_id);
DBMS_OUTPUT.put_line ('Invoice Line_ID' || vinvoice_line_id);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
-- *********************************************
PROCEDURE deltempflag_im
IS
BEGIN
DELETE ap_im_int_ora
WHERE flag = 'P' AND TRUNC (date_from_gr) < TRUNC (SYSDATE) - 120;
UPDATE ap_im_int_ora
SET flag = 'X'
WHERE flag IS NULL
AND invoice_num LIKE '%CL'
AND TRUNC (date_from_gr) = TRUNC (SYSDATE) - 2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
-- *********************************************
FUNCTION checkdescr (descr IN VARCHAR2, inv_amount IN NUMBER)
RETURN NUMBER
IS
nreturn NUMBER := 0;
cdesc VARCHAR2 (80);
camt NUMBER;
BEGIN
SELECT description, invoice_amount
INTO cdesc, camt
FROM ap_invoices_all
WHERE description = descr
AND invoice_type_lookup_code = 'STANDARD'
AND ROWNUM = 1
ORDER BY creation_date DESC;
IF cdesc = descr AND NVL (camt, 0) <> inv_amount
THEN
nreturn := 1;
ELSE
nreturn := 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN nreturn;
END;
-- *********************************************
PROCEDURE createcancelim (descr IN VARCHAR2)
IS
cnewseq ap_invoices_interface.invoice_id%TYPE;
vinvoice_id ap_invoices_all.invoice_id%TYPE;
vinvoice_num ap_invoices_all.invoice_num%TYPE;
vinvoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
vinvoice_date ap_invoices_all.invoice_date%TYPE;
vvendor_num ap_invoices_interface.vendor_num%TYPE;
vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE;
vvendor_id ap_invoices_all.vendor_id%TYPE;
vvendor_site_id ap_invoices_all.vendor_site_id%TYPE;
vinvoice_amount ap_invoices_all.invoice_currency_code%TYPE;
vinvoice_currency_code ap_invoices_all.invoice_currency_code%TYPE;
vterms_id ap_invoices_all.terms_id%TYPE;
vdescription ap_invoices_all.description%TYPE;
vpay_group_lookup_code ap_invoices_all.pay_group_lookup_code%TYPE;
vgl_date ap_invoices_all.gl_date%TYPE;
vterms_date ap_invoices_all.terms_date%TYPE;
vattribute2 ap_invoices_all.attribute2%TYPE;
vattribute4 ap_invoices_all.attribute4%TYPE;
vorg_id ap_invoices_all.org_id%TYPE;
CURSOR detail_data
IS
SELECT *
FROM ap_invoice_distributions_all
WHERE invoice_id = vinvoice_id;
rdetail detail_data%ROWTYPE;
nno NUMBER;
BEGIN
SELECT invoice_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_id, vendor_site_id, invoice_amount,
invoice_currency_code, terms_id, description,
pay_group_lookup_code, gl_date, terms_date, attribute2,
org_id, attribute4
INTO vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
vinvoice_date, vvendor_id, vvendor_site_id, vinvoice_amount,
vinvoice_currency_code, vterms_id, vdescription,
vpay_group_lookup_code, vgl_date, vterms_date, vattribute2,
vorg_id, vattribute4
FROM (SELECT *
FROM ap_invoices_all
WHERE description = descr
AND invoice_type_lookup_code = 'STANDARD'
ORDER BY creation_date DESC, invoice_id DESC)
WHERE ROWNUM = 1;
SELECT segment1
INTO vvendor_num
FROM po_vendors
WHERE vendor_id = vvendor_id;
SELECT vendor_site_code
INTO vvendor_site_code
FROM po_vendor_sites_all
WHERE vendor_id = vvendor_id AND vendor_site_id = vvendor_site_id;
cnewseq := TO_NUMBER ( TO_CHAR (SYSTIMESTAMP, 'yymm')|| TO_CHAR (supp_int_pkg.getnewseq ()) );
IF INSTR (vinvoice_num, '(N', 1, 1) != 0
THEN
vinvoice_num := SUBSTR (vinvoice_num, 1, INSTR (vinvoice_num, '(N', 1, 1) - 1);
END IF;
ap_ret_int_pkg.temp2orawksh (cnewseq,
vinvoice_num
|| ' (C-'
|| TO_CHAR (SYSTIMESTAMP, 'yyyymmdd')
|| ')',
'CREDIT',
vinvoice_date,
vvendor_num,
vvendor_site_code,
ROUND (vinvoice_amount, 2) * -1,
vinvoice_currency_code,
vterms_id,
vdescription,
vpay_group_lookup_code,
SYSDATE - 1,
vterms_date,
vattribute2,
vorg_id,
vattribute4
);
-- ********* DETAIL **********
OPEN detail_data;
LOOP
FETCH detail_data
INTO rdetail;
EXIT WHEN detail_data%NOTFOUND;
nno := TO_CHAR (supp_int_pkg.getnewseq ());
ap_ret_int_pkg.temp2orawksd (cnewseq,
nno,
rdetail.distribution_line_number,
rdetail.line_type_lookup_code,
ROUND (rdetail.amount, 2) * -1,
SYSDATE - 1,
rdetail.description,
rdetail.amount_includes_tax_flag,
NULL,
rdetail.dist_code_combination_id,
rdetail.attribute1,
rdetail.org_id,
rdetail.tax_code_override_flag,
rdetail.tax_code_id,
NULL,
rdetail.tax_code_override_flag,
vattribute4
);
END LOOP;
END;
FUNCTION checkinvnumb (v_invoice IN VARCHAR2)
RETURN NUMBER
IS
nreturn NUMBER;
BEGIN
SELECT 1
INTO nreturn
FROM ap_invoices_all
WHERE invoice_num = v_invoice AND ROWNUM = 1
ORDER BY ROWID DESC;
RETURN (nreturn);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN (0);
WHEN OTHERS
THEN
RETURN (1);
END;
END ap_im_int_pkg;
/
[Updated on: Fri, 17 February 2012 03:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: ORACLE error 6550 in FDPSTP [message #544603 is a reply to message #543852] |
Wed, 22 February 2012 21:38 |
|
GoGreen
Messages: 28
Registered: February 2012
Location: Java Island
|
Junior Member |
|
|
sorry, i has been try, run in sqlplus ( copy paste ) but result justification is left.
How the techniques if i will create pararell process and pararell process to be running when execute procedure;
thanks
gvE5
SQL> CREATE OR REPLACE PACKAGE apps.je_ap_im_int_pkg
2 AS
3 /******************************************************************************/
4 PROCEDURE je_ap_im_int_start;
5 --(errbuf out nocopy varchar2, retcode out nocopy number)
6
7 PROCEDURE gold2ora_im;
8
9 PROCEDURE fillandsend_im1;
10
11 PROCEDURE temp2orawksh (
12 vinvoice_id ap_invoices_interface.invoice_id%TYPE,
13 vinvoice_num ap_invoices_interface.invoice_num%TYPE,
14 vinvoice_type_lookup_code ap_invoices_interface.invoice_type_lookup_code%TYPE,
15 vinvoice_date ap_invoices_interface.invoice_date%TYPE,
16 vvendor_num ap_invoices_interface.vendor_num%TYPE,
17 vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE,
18 vinvoice_amount ap_invoices_interface.invoice_amount%TYPE,
19 vinvoice_currency_code ap_invoices_interface.invoice_currency_code%TYPE,
20 vterms_id ap_invoices_interface.terms_id%TYPE,
21 vterms_name ap_invoices_interface.terms_name%TYPE,
22 vdescription ap_invoices_interface.description%TYPE,
23 vpay_group_lookup_code ap_invoices_interface.pay_group_lookup_code%TYPE,
24 vgl_date ap_invoices_interface.gl_date%TYPE,
25 vterms_date ap_invoices_interface.terms_date%TYPE,
26 vattribute2 ap_invoices_interface.attribute2%TYPE,
27 vorg_id ap_invoices_interface.org_id%TYPE,
28 vattribute3 VARCHAR2,
29 vattribute4 VARCHAR2
30 );
31
32 PROCEDURE temp2orawksd (
33 vinvoice_id ap_invoice_lines_interface.invoice_id%TYPE,
34 vinvoice_line_id ap_invoice_lines_interface.invoice_line_id%TYPE,
35 vamount ap_invoice_lines_interface.amount%TYPE,
36 vaccounting_date ap_invoice_lines_interface.accounting_date%TYPE,
37 vdescription ap_invoice_lines_interface.description%TYPE,
38 vdist_code_concatenated ap_invoice_lines_interface.dist_code_concatenated%TYPE,
39 vattribute1 ap_invoice_lines_interface.attribute1%TYPE,
40 vorg_id ap_invoice_lines_interface.org_id%TYPE,
41 vattribute3 VARCHAR2,
42 vattribute4 VARCHAR2
43 );
44
45 PROCEDURE deltempflag_im;
46
47 FUNCTION checkdescr (descr IN VARCHAR2, inv_amount IN NUMBER)
48 RETURN NUMBER;
49
50 PROCEDURE createcancelim (descr IN VARCHAR2);
51
52 FUNCTION checkinvnumb (v_invoice IN VARCHAR2)
53 RETURN NUMBER;
54 END je_ap_im_int_pkg;
55 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY apps.je_ap_im_int_pkg
2 AS
3 PROCEDURE je_ap_im_int_start
4 IS
5 l_job NUMBER;
6 BEGIN
7 gold2ora_im;
8
9 BEGIN
10 DBMS_JOB.submit (l_job, 'FillAndSend_IM1;');
11 EXCEPTION
12 WHEN OTHERS
13 THEN
14 DBMS_OUTPUT.put_line (SQLERRM);
15 END;
16
17 -- 3. delete all record 'P' di Temp
18 deltempflag_im;
19 -- 4. Commit
20 COMMIT;
21 END;
22 -- *********************************************
23 PROCEDURE gold2ora_im
24 IS
25 BEGIN
26 INSERT INTO je_ap_im_int_ora
27 (im_interface_id, invoice_num, invoice_type_lookup_code,
28 invoice_date, vendor_num, vendor_site_code, amount,
29 invoice_currency_code, terms_name, description,
30 pay_group_lookup_code, gl_date, terms_date, gold_store,
31 accounting_date, type_code, sales_chanel, division,
32 inventory, dept)
33 SELECT im_interface_id, invoice_num, invoice_type_lookup_code,
34 invoice_date, vendor_num, vendor_site_code, amount,
35 invoice_currency_code, terms_name, description,
36 pay_group_lookup_code, gl_date, terms_date, gold_store,
37 accounting_date, type_code, sales_chanel, division,
38 inventory, DECODE (sales_chanel, '6', 'CAFETARIA', dept)
39 FROM [email]je_ap_im_int@link2prd.com[/email]
40 ORDER BY invoice_num, type_code;
41
42 IF TRUNC (SYSDATE) <= TO_DATE ('01/04/2011', 'dd/mm/yyyy')
43 THEN
44 UPDATE je_ap_im_int_ora
45 SET gl_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy'),
46 accounting_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy')
47 WHERE flag IS NULL AND SUBSTR (gold_store, 3, 3) = '227';
48 END IF;
49
50 UPDATE je_ap_im_int_ora
51 SET gl_date = TRUNC (SYSDATE - 1),
52 accounting_date = TRUNC (SYSDATE - 1)
53 WHERE TO_CHAR (gl_date, 'yyyymm') <
54 TO_CHAR (TRUNC (SYSDATE - 6), 'yyyymm')
55 AND flag IS NULL;
56 END;
57
58 -- *********************************************
59 PROCEDURE fillandsend_im1
60 IS
61 CURSOR gold_data
62 IS
63 SELECT *
64 FROM je_ap_im_int_ora
65 WHERE flag IS NULL
66 AND SUBSTR (gold_store, 3, 3) IN (
67 SELECT store_code
68 FROM je_parameter_ora
69 WHERE module = 'MATC'
70 AND status = 1)
71 AND amount <> 0
72 AND type_code = '8'
73 AND SUBSTR (gold_store, 3, 3) BETWEEN '100' AND '140' /* first parameter */
74 ORDER BY SUBSTR (gold_store, 3, 3),
75 vendor_num,
76 vendor_site_code,
77 invoice_num;
78
79 rgr gold_data%ROWTYPE;
80 ljob NUMBER;
81 cnewseq ap_invoices_interface.invoice_id%TYPE;
82 ctermsid ap_invoices_interface.terms_id%TYPE;
83 nno NUMBER;
84 cvendor_num rgr.vendor_num%TYPE;
85 cvendor_site_code rgr.vendor_site_code%TYPE;
86 cinvoice_num rgr.invoice_num%TYPE;
87 cinv_num rgr.invoice_num%TYPE;
88 ccancel CHAR (1);
89 vreturn NUMBER;
90 vstorenew VARCHAR2 (3);
91 vsubstore VARCHAR2 (3);
92 vtoko_tampil VARCHAR2 (10) := 1;
93 dummy VARCHAR2 (100);
94 lcreate BOOLEAN := TRUE;
95 vattribute4 VARCHAR2 (3);
96 cinvoicenum rgr.invoice_num%TYPE;
97 cgoldstored rgr.gold_store%TYPE;
98 BEGIN
99 cvendor_num := 'X';
100 cvendor_site_code := 'X';
101 cinvoice_num := 'X';
102
103 OPEN gold_data;
104
105 LOOP
106 FETCH gold_data
107 INTO rgr;
108
109 EXIT WHEN gold_data%NOTFOUND;
110 vreturn := 1;
111 dummy := NULL; -- RESET 05092011
112 ctermsid := je_supp_int_pkg.findtermid (rgr.terms_name);
113 lcreate := TRUE;
114
115 IF SUBSTR (rgr.invoice_num, INSTR (rgr.invoice_num, 'CL', 1), 2) =
116 'CL'
117 AND ctermsid <> 0
118 THEN
119 vreturn := checkinvnumb (SUBSTR (rgr.invoice_num, 1, 13));
120 ctermsid := vreturn;
121 END IF;
122
123 IF ctermsid <> 0
124 THEN
125 ctermsid := je_supp_int_pkg.findtermid (rgr.terms_name);
126 ccancel := 'N';
127
128 IF checkdescr (rgr.description, ROUND (rgr.amount, 2)) = 1
129 THEN
130 createcancelim (rgr.description);
131 ccancel := 'Y';
132 ELSE
133 IF SUBSTR (rgr.invoice_num, -1) = 'R'
134 THEN
135 BEGIN
136 SELECT invoice_num
137 INTO dummy
138 FROM ap_invoices_all
139 WHERE description = rgr.description
140 AND invoice_type_lookup_code = 'STANDARD'
141 AND invoice_amount = ROUND (rgr.amount, 2)
142 AND invoice_num =
143 SUBSTR (rgr.invoice_num,
144 1,
145 LENGTH (rgr.invoice_num) - 1
146 )
147 AND ROWNUM = 1
148 ORDER BY creation_date DESC, invoice_id DESC;
149
150 createcancelim (rgr.description);
151 ccancel := 'Y';
152 lcreate := TRUE;
153 EXCEPTION
154 WHEN NO_DATA_FOUND
155 THEN
156 lcreate := TRUE;
157 END;
158 ELSE
159 BEGIN
160 SELECT 1
161 INTO dummy
162 FROM ap_invoices_all
163 WHERE description = rgr.description
164 AND invoice_type_lookup_code = 'STANDARD'
165 AND invoice_amount = ROUND (rgr.amount, 2)
166 AND invoice_num = rgr.invoice_num
167 AND ROWNUM = 1
168 ORDER BY creation_date DESC, invoice_id DESC;
169
170 lcreate := FALSE;
171 EXCEPTION
172 WHEN NO_DATA_FOUND
173 THEN
174 BEGIN
175 SELECT invoice_num
176 INTO dummy
177 FROM ap_invoices_all
178 WHERE description = rgr.description
179 AND invoice_type_lookup_code = 'STANDARD'
180 AND invoice_amount = ROUND (rgr.amount, 2)
181 AND ROWNUM = 1
182 ORDER BY creation_date DESC, invoice_id DESC;
183
184 -- Cancel dan buat header & detail invoice
185 createcancelim (rgr.description);
186 ccancel := 'Y';
187 lcreate := TRUE;
188 EXCEPTION
189 WHEN NO_DATA_FOUND
190 THEN
191 lcreate := TRUE;
192 END;
193 END;
194 END IF;
195 END IF;
196
197 cinv_num := rgr.invoice_num;
198 cnewseq :=
199 TO_NUMBER ( TO_CHAR (SYSTIMESTAMP, 'yymm')
200 || TO_CHAR (je_supp_int_pkg.getnewseq ())
201 );
202
203 IF ( SUBSTR (rgr.gold_store, 3, 3) = '100'
204 OR SUBSTR (rgr.gold_store, 3, 3) = '199'
205 )
206 AND rgr.sales_chanel = 3
207 THEN
208 vstorenew := '201';
209 vsubstore := '201';
210 ELSE
211 IF je_supp_int_pkg.je_func_gry (SUBSTR (rgr.gold_store,
212 3,
213 3
214 )
215 ) = '201'
216 THEN
217 vstorenew := '201';
218 vsubstore := '201';
219 -- substr(rGr.GOLD_STORE,3,3); 28/09/2011
220 ELSE
221 vstorenew := SUBSTR (rgr.gold_store, 3, 3);
222
223 IF SUBSTR (rgr.gold_store, 3, 3) IN ('199', '100')
224 THEN
225 vsubstore := '101';
226 ELSE
227 vsubstore := SUBSTR (rgr.gold_store, 3, 3);
228 END IF;
229 END IF;
230 END IF;
231
232 vattribute4 := vsubstore;
233
234 IF lcreate
235 THEN
236 IF SUBSTR (rgr.vendor_site_code, 1, 1) <> 'C'
237 AND (rgr.vendor_num <> cvendor_num)
238 OR (rgr.vendor_site_code <> cvendor_site_code)
239 OR (rgr.invoice_num <> cinvoice_num)
240 THEN
241 -- Tulis ke Header
242 temp2orawksh (cnewseq,
243 cinv_num,
244 rgr.invoice_type_lookup_code,
245 rgr.invoice_date,
246 rgr.vendor_num,
247 rgr.vendor_site_code,
248 ROUND (rgr.amount, 2),
249 rgr.invoice_currency_code,
250 ctermsid,
251 rgr.terms_name,
252 rgr.description,
253 rgr.pay_group_lookup_code,
254 rgr.gl_date,
255 rgr.terms_date,
256 rgr.dept,
257 je_supp_int_pkg.findorg_id (vstorenew),
258 rgr.inventory,
259 vattribute4
260 );
261 cvendor_num := rgr.vendor_num;
262 cvendor_site_code := rgr.vendor_site_code;
263 cinvoice_num := rgr.invoice_num;
264 END IF;
265 END IF;
266
267 nno := TO_CHAR (je_supp_int_pkg.getnewseq ());
268
269 IF lcreate
270 THEN -- lCreate -- 05092011
271 IF vsubstore IN ('100', '199')
272 THEN
273 vsubstore := '101';
274 END IF;
275
276 temp2orawksd (cnewseq,
277 nno,
278 ROUND (rgr.amount, 2),
279 rgr.accounting_date,
280 rgr.description,
281 je_supp_int_pkg.je_func_gry (vstorenew)
282 || '.000.9922001.000.'
283 || vsubstore
284 || '.00',
285 rgr.dept,
286 je_supp_int_pkg.findorg_id (vstorenew),
287 rgr.inventory,
288 vattribute4
289 );
290
291 UPDATE je_ap_im_int_ora
292 SET flag = 'P'
293 WHERE im_interface_id = rgr.im_interface_id
294 RETURNING invoice_num, gold_store
295 INTO cinvoicenum, cgoldstored;
296
297 UPDATE je_ap_im_int_ora
298 SET flag = 'P'
299 WHERE invoice_num = cinvoicenum
300 AND gold_store = cgoldstored
301 AND flag IS NULL;
302
303 DBMS_OUTPUT.put_line ('Flag = P');
304 DBMS_OUTPUT.put_line (' ');
305 END IF;
306 END IF;
307
308 IF SUBSTR (rgr.gold_store, 3, 3) <> vtoko_tampil
309 THEN
310 fnd_file.put_line
311 (fnd_file.LOG,
312 'Invoice Matching Interface for Operating Unit : '
313 || SUBSTR (rgr.gold_store, 3, 3)
314 );
315 vtoko_tampil := SUBSTR (rgr.gold_store, 3, 3);
316 END IF;
317 END LOOP;
318 END;
319
320
321 -- *********************************************
322 PROCEDURE temp2orawksh (
323 vinvoice_id ap_invoices_interface.invoice_id%TYPE,
324 vinvoice_num ap_invoices_interface.invoice_num%TYPE,
325 vinvoice_type_lookup_code ap_invoices_interface.invoice_type_lookup_code%TYPE,
326 vinvoice_date ap_invoices_interface.invoice_date%TYPE,
327 vvendor_num ap_invoices_interface.vendor_num%TYPE,
328 vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE,
329 vinvoice_amount ap_invoices_interface.invoice_amount%TYPE,
330 vinvoice_currency_code ap_invoices_interface.invoice_currency_code%TYPE,
331 vterms_id ap_invoices_interface.terms_id%TYPE,
332 vterms_name ap_invoices_interface.terms_name%TYPE,
333 vdescription ap_invoices_interface.description%TYPE,
334 vpay_group_lookup_code ap_invoices_interface.pay_group_lookup_code%TYPE,
335 vgl_date ap_invoices_interface.gl_date%TYPE,
336 vterms_date ap_invoices_interface.terms_date%TYPE,
337 vattribute2 ap_invoices_interface.attribute2%TYPE,
338 vorg_id ap_invoices_interface.org_id%TYPE,
339 vattribute3 VARCHAR2,
340 vattribute4 VARCHAR2
341 )
342 IS
343 BEGIN
344 DBMS_OUTPUT.put_line ('Masuk ke Header');
345
346 INSERT INTO ap_invoices_interface
347 (invoice_id, invoice_num, invoice_type_lookup_code,
348 invoice_date, vendor_num, vendor_site_code,
349 invoice_amount, invoice_currency_code, terms_id,
350 terms_name, description, creation_date, created_by,
351 SOURCE, doc_category_code, payment_method_lookup_code,
352 pay_group_lookup_code, gl_date, terms_date,
353 attribute2, org_id, attribute3, attribute4
354 )
355 VALUES (vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
356 vinvoice_date, vvendor_num, vvendor_site_code,
357 vinvoice_amount, vinvoice_currency_code, vterms_id,
358 vterms_name, vdescription, SYSDATE, '1091',
359 'G.O.L.D', 'STD INV', 'CHECK',
360 vpay_group_lookup_code, vgl_date, vterms_date,
361 vattribute2, vorg_id, vattribute3, vattribute4
362 );
363 EXCEPTION
364 WHEN DUP_VAL_ON_INDEX
365 THEN
366 DBMS_OUTPUT.put_line ('ERROR : Invoice ID : ' || vinvoice_id);
367 WHEN OTHERS
368 THEN
369 fnd_file.put_line
370 (fnd_file.LOG,
371 'ERROR Insert Into AP_INVOICES_INTERFACE, Invoice num : '
372 || vinvoice_num
373 || ' Attribute 3 :'
374 || vattribute3
375 );
376 END;
377
378 -- *********************************************\
379 PROCEDURE temp2orawksd (
380 vinvoice_id ap_invoice_lines_interface.invoice_id%TYPE,
381 vinvoice_line_id ap_invoice_lines_interface.invoice_line_id%TYPE,
382 vamount ap_invoice_lines_interface.amount%TYPE,
383 vaccounting_date ap_invoice_lines_interface.accounting_date%TYPE,
384 vdescription ap_invoice_lines_interface.description%TYPE,
385 vdist_code_concatenated ap_invoice_lines_interface.dist_code_concatenated%TYPE,
386 vattribute1 ap_invoice_lines_interface.attribute1%TYPE,
387 vorg_id ap_invoice_lines_interface.org_id%TYPE,
388 vattribute3 VARCHAR2,
389 vattribute4 VARCHAR2
390 )
391 IS
392 BEGIN
393 DBMS_OUTPUT.put_line ('Masuk ke Detail');
394 DBMS_OUTPUT.put_line ('Invoice ID : ' || vinvoice_id);
395 DBMS_OUTPUT.put_line ('Invoice Line_ID' || vinvoice_line_id);
396 DBMS_OUTPUT.put_line (' ');
397
398 INSERT INTO ap_invoice_lines_interface
399 (invoice_id, invoice_line_id, line_number,
400 line_type_lookup_code, amount, accounting_date,
401 description, dist_code_concatenated, created_by,
402 creation_date, attribute1, org_id, attribute3, attribute4
403 )
404 VALUES (vinvoice_id, vinvoice_line_id, 1,
405 'ITEM', vamount, vaccounting_date,
406 vdescription, vdist_code_concatenated, '1091',
407 SYSDATE, vattribute1, vorg_id, vattribute3, vattribute4
408 );
409 EXCEPTION
410 WHEN DUP_VAL_ON_INDEX
411 THEN
412 DBMS_OUTPUT.put_line ('ERROR : Invoice ID : ' || vinvoice_id);
413 DBMS_OUTPUT.put_line ('Invoice Line_ID' || vinvoice_line_id);
414 WHEN OTHERS
415 THEN
416 fnd_file.put_line
417 (fnd_file.LOG,
418 'ERROR insert into AP_INVOICE_LINES_INTERFACE, Invoice ID: '
419 || vinvoice_id
420 || ' Attribute 3 :'
421 || vattribute3
422 );
423 END;
424
425 -- *********************************************
426 PROCEDURE deltempflag_im
427 IS
428 BEGIN
429 DELETE je_ap_im_int_ora
430 WHERE flag = 'P' AND TRUNC (date_from_gold) <
431 TRUNC (SYSDATE)
432 - 120;
433
434 UPDATE je_ap_im_int_ora
435 SET flag = 'X'
436 WHERE flag IS NULL
437 AND invoice_num LIKE '%CL'
438 AND TRUNC (date_from_gold) = TRUNC (SYSDATE) - 2;
439 END;
440
441 -- *********************************************
442 FUNCTION checkdescr (descr IN VARCHAR2, inv_amount IN NUMBER)
443 RETURN NUMBER
444 IS
445 nreturn NUMBER := 0;
446 cdesc VARCHAR2 (80);
447 camt NUMBER;
448 BEGIN
449 SELECT description, invoice_amount
450 INTO cdesc, camt
451 FROM ap_invoices_all
452 WHERE description = descr
453 AND invoice_type_lookup_code = 'STANDARD'
454 AND ROWNUM = 1
455 ORDER BY creation_date DESC;
456
457 IF cdesc = descr AND NVL (camt, 0) <> inv_amount
458 THEN
459 nreturn := 1;
460 ELSE
461 nreturn := 0;
462 END IF;
463
464 RETURN (nreturn);
465 EXCEPTION
466 WHEN OTHERS
467 THEN
468 RETURN nreturn;
469 END;
470
471 -- *********************************************
472 PROCEDURE createcancelim (descr IN VARCHAR2)
473 IS
474 cnewseq ap_invoices_interface.invoice_id%TYPE;
475 vinvoice_id ap_invoices_all.invoice_id%TYPE;
476 vinvoice_num ap_invoices_all.invoice_num%TYPE;
477 vinvoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
478 vinvoice_date ap_invoices_all.invoice_date%TYPE;
479 vvendor_num ap_invoices_interface.vendor_num%TYPE;
480 vvendor_site_code ap_invoices_interface.vendor_site_code%TYPE;
481 vvendor_id ap_invoices_all.vendor_id%TYPE;
482 vvendor_site_id ap_invoices_all.vendor_site_id%TYPE;
483 vinvoice_amount ap_invoices_all.invoice_currency_code%TYPE;
484 vinvoice_currency_code ap_invoices_all.invoice_currency_code%TYPE;
485 vterms_id ap_invoices_all.terms_id%TYPE;
486 vdescription ap_invoices_all.description%TYPE;
487 vpay_group_lookup_code ap_invoices_all.pay_group_lookup_code%TYPE;
488 vgl_date ap_invoices_all.gl_date%TYPE;
489 vterms_date ap_invoices_all.terms_date%TYPE;
490 vattribute2 ap_invoices_all.attribute2%TYPE;
491 vattribute4 ap_invoices_all.attribute4%TYPE;
492 vorg_id ap_invoices_all.org_id%TYPE;
493
494 CURSOR detail_data
495 IS
496 SELECT *
497 FROM ap_invoice_distributions_all
498 WHERE invoice_id = vinvoice_id;
499
500 rdetail detail_data%ROWTYPE;
501 nno NUMBER;
502 BEGIN
503 SELECT invoice_id, invoice_num, invoice_type_lookup_code,
504 invoice_date, vendor_id, vendor_site_id, invoice_amount,
505 invoice_currency_code, terms_id, description,
506 pay_group_lookup_code, gl_date, terms_date, attribute2,
507 org_id, attribute4
508 INTO vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
509 vinvoice_date, vvendor_id, vvendor_site_id, vinvoice_amount,
510 vinvoice_currency_code, vterms_id, vdescription,
511 vpay_group_lookup_code, vgl_date, vterms_date, vattribute2,
512 vorg_id, vattribute4
513 FROM (SELECT *
514 FROM ap_invoices_all
515 WHERE description = descr
516 AND invoice_type_lookup_code = 'STANDARD'
517 ORDER BY creation_date DESC, invoice_id DESC)
518 WHERE ROWNUM = 1;
519
520 SELECT segment1
521 INTO vvendor_num
522 FROM po_vendors
523 WHERE vendor_id = vvendor_id;
524
525 SELECT vendor_site_code
526 INTO vvendor_site_code
527 FROM po_vendor_sites_all
528 WHERE vendor_id = vvendor_id AND vendor_site_id = vvendor_site_id;
529
530 -- Bikin Seq baru, tgl hari ini + Sequence
531 cnewseq :=
532 TO_NUMBER ( TO_CHAR (SYSTIMESTAMP, 'yymm')
533 || TO_CHAR (je_supp_int_pkg.getnewseq ())
534 );
535
536 IF INSTR (vinvoice_num, '(N', 1, 1) != 0
537 THEN
538 vinvoice_num :=
539 SUBSTR (vinvoice_num, 1, INSTR (vinvoice_num, '(N', 1, 1) - 1);
540 END IF;
541
542 je_ap_ret_int_pkg.temp2orawksh (cnewseq,
543 vinvoice_num
544 || ' (C-'
545 || TO_CHAR (SYSTIMESTAMP, 'yyyymmdd')
546 || ')',
547 'CREDIT',
548 vinvoice_date,
549 vvendor_num,
550 vvendor_site_code,
551 ROUND (vinvoice_amount, 2) * -1,
552 vinvoice_currency_code,
553 vterms_id,
554 NULL,
555 vdescription,
556 vpay_group_lookup_code,
557 SYSDATE - 1,
558 vterms_date,
559 vattribute2,
560 vorg_id,
561 vattribute4
562 );
563
564 OPEN detail_data;
565
566 LOOP
567 FETCH detail_data
568 INTO rdetail;
569
570 EXIT WHEN detail_data%NOTFOUND;
571 nno := TO_CHAR (je_supp_int_pkg.getnewseq ());
572 je_ap_ret_int_pkg.temp2orawksd (cnewseq,
573 nno,
574 rdetail.distribution_line_number,
575 rdetail.line_type_lookup_code,
576 ROUND (rdetail.amount, 2) * -1,
577 SYSDATE - 1,
578 rdetail.description,
579 rdetail.amount_includes_tax_flag,
580 NULL,
581 rdetail.dist_code_combination_id,
582 rdetail.attribute1,
583 rdetail.org_id,
584 rdetail.tax_code_override_flag,
585 rdetail.tax_code_id,
586 NULL,
587 rdetail.tax_code_override_flag,
588 vattribute4
589 );
590 fnd_file.put_line (fnd_file.LOG,
591 'Invoice Matching Cancelled : '
592 || SUBSTR (je_store_name (vorg_id),
593 INSTR (je_store_name (vorg_id),
594 CHR (10),
595 1,
596 1
597 )
598 + 1,
599 10
600 )
601 || ' - '
602 || vinvoice_num
603 );
604 END LOOP;
605 END;
606
607 FUNCTION checkinvnumb (v_invoice IN VARCHAR2)
608 RETURN NUMBER
609 IS
610 nreturn NUMBER;
611 BEGIN
612 SELECT 1
613 INTO nreturn
614 FROM ap_invoices_all
615 WHERE invoice_num = v_invoice AND ROWNUM = 1
616 ORDER BY ROWID DESC;
617
618 RETURN (nreturn);
619 EXCEPTION
620 WHEN NO_DATA_FOUND
621 THEN
622 RETURN (0);
623 WHEN OTHERS
624 THEN
625 RETURN (1);
626 END;
627 END je_ap_im_int_pkg;
* {code} tags added by BlackSwan Please do so yourself in the future
[Updated on: Wed, 22 February 2012 22:04] by Moderator Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Sep 09 21:47:24 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
