/* Formatted on 2009/06/01 13:03 (Formatter Plus v4.8.8) */
/* Formatted on 2009/06/01 14:39 (Formatter Plus v4.8.8) */
begin
mo_global.set_policy_context('S',81);
end;
select distinct segment1 from po_headers_all where po_headers_all.creation_date BETWEEN TO_DATE ('01-APR-2008')
AND TO_DATE ('30-APR-2009') --order by to_number(segment1)
minus
SELECT distinct po_headers_all.segment1 AS "PO #",
po_headers_all.type_lookup_code AS "PO Type",
TO_CHAR (po_headers_all.creation_date, 'DD-Mon-YYYY') AS "PO Date",
TO_CHAR (po_headers_all.creation_date, 'Month') AS "Month",
NVL (po_headers_all.authorization_status, 'Incomplete') status,
prh.segment1 "PR No", to_char(prh.creation_date,'DD-Mon-YYYY') "PR Date",
hr_employees_current_v.full_name AS "Buyer Name",
po_headers_all.agent_id AS "Buyer No", po_lines_all.line_num AS line,
mcb.segment1 || '.' || mcb.segment2 "PO Item",
po_lines_all.item_description AS description,
po_lines_all.quantity AS "Order Quantity",
po_lines_all.quantity * po_lines_all.unit_price AS "Line Total",
NVL (pll.shipment_status, 'Open') "Receipt Status",
gcc1.segment1
|| '-'
|| gcc1.segment2
|| '-'
|| gcc1.segment3
|| '-'
|| gcc1.segment4
|| '-'
|| gcc1.segment5
|| '-'
|| gcc1.segment6 "GL Debit Account",
gcc2.segment1
|| '-'
|| gcc2.segment2
|| '-'
|| gcc2.segment3
|| '-'
|| gcc2.segment4
|| '-'
|| gcc2.segment5
|| '-'
|| gcc2.segment6 "Supplier Credit Account",
aia.payment_status_flag,
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = aid.invoice_distribution_id)
"Invoice Approved?",
aia.amount_paid, apt.NAME "Payment Term", rsh.receipt_num "GRN Num",
to_char(TRUNC (rct.transaction_date),'DD-Mon-YYYY') "GRN Date"
FROM po_vendors,
po_headers_all,
po_lines_all,
po_distributions_all,
hr_employees_current_v,
mtl_categories_b mcb,
po_requisition_headers_all prh,
po_requisition_lines_all prn,
po_req_distributions_all prd,
po_line_locations_v pll,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
ap_invoice_distributions_all aid,
ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_terms apt,
rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE po_vendors.vendor_id = po_headers_all.vendor_id
AND po_headers_all.po_header_id = po_lines_all.po_header_id
AND po_lines_all.po_line_id = po_distributions_all.po_line_id
AND po_headers_all.agent_id = hr_employees_current_v.employee_id
-- AND po_headers_all.creation_date BETWEEN TO_DATE ('01-NOV-2008')
-- AND TO_DATE ('30-APR-2009')
AND mcb.category_id = po_lines_all.category_id
-- AND po_headers_all.segment1 = 749
AND prh.requisition_header_id = prn.requisition_header_id
AND prn.requisition_line_id = prd.requisition_line_id
AND prd.distribution_id = po_distributions_all.req_distribution_id
AND pll.po_header_id = po_lines_all.po_header_id
AND pll.po_line_id = po_lines_all.po_line_id
AND gcc1.code_combination_id = po_distributions_all.code_combination_id
AND aid.po_distribution_id(+) = po_distributions_all.po_distribution_id
AND gcc2.code_combination_id(+) = aia.accts_pay_code_combination_id
AND aia.invoice_id(+) = aid.invoice_id
AND aipa.invoice_id(+) = aia.invoice_id
AND apt.term_id(+) = po_headers_all.terms_id
AND rct.shipment_line_id = rsl.shipment_line_id(+)
AND rct.shipment_header_id = rsh.shipment_header_id(+)
AND rct.po_header_id(+) = po_headers_all.po_header_id
AND rct.transaction_type(+) = 'DELIVER'
AND po_lines_all.item_description LIKE '%Sandip%'
GROUP BY po_headers_all.segment1,
po_lines_all.line_num,
po_vendors.vendor_name,
po_headers_all.agent_id,
hr_employees_current_v.full_name,
po_headers_all.creation_date,
po_lines_all.closed_code,
po_headers_all.type_lookup_code,
po_lines_all.item_description,
po_lines_all.unit_meas_lookup_code,
po_lines_all.quantity,
po_lines_all.unit_price,
mcb.segment1,
mcb.segment2,
prh.segment1,
po_headers_all.authorization_status,
prh.creation_date,
pll.shipment_status,
gcc1.segment1
|| '-'
|| gcc1.segment2
|| '-'
|| gcc1.segment3
|| '-'
|| gcc1.segment4
|| '-'
|| gcc1.segment5
|| '-'
|| gcc1.segment6,
gcc2.segment1
|| '-'
|| gcc2.segment2
|| '-'
|| gcc2.segment3
|| '-'
|| gcc2.segment4
|| '-'
|| gcc2.segment5
|| '-'
|| gcc2.segment6,
aid.dist_code_combination_id,
aia.payment_status_flag,
aid.invoice_distribution_id,
aia.amount_paid,
apt.NAME,
rsh.receipt_num,
TRUNC (rct.transaction_date)
ORDER BY --po_headers_all.creation_date,
to_number(po_headers_all.segment1),
po_lines_all.line_num
select distinct segment1 from po_headers_all
Saturday, November 20, 2010
Delete Person in Oracle HRMS
DECLARE
cursor c is
select * from per_all_people_f
where current_employee_flag is null
and rownum < 100;
x_validate_mode BOOLEAN := FALSE;
x_person_id INTEGER ;
x_man_message VARCHAR2(300);
BEGIN
for i in c
loop
x_person_id := i.person_id;
HR_PERSON_API.DELETE_PERSON(
P_VALIDATE => x_validate_mode,
P_EFFECTIVE_DATE => '09-Mar-2010',
P_PERSON_ID => x_person_id,
P_PERFORM_PREDEL_VALIDATION => FALSE,
P_PERSON_ORG_MANAGER_WARNING => x_man_message
);
dbms_output.put_line('Success'||x_person_id);
end loop;
--commit;
END;
select DISTINCT PERSON_ID from per_all_people_f --where person_id = 118
cursor c is
select * from per_all_people_f
where current_employee_flag is null
and rownum < 100;
x_validate_mode BOOLEAN := FALSE;
x_person_id INTEGER ;
x_man_message VARCHAR2(300);
BEGIN
for i in c
loop
x_person_id := i.person_id;
HR_PERSON_API.DELETE_PERSON(
P_VALIDATE => x_validate_mode,
P_EFFECTIVE_DATE => '09-Mar-2010',
P_PERSON_ID => x_person_id,
P_PERFORM_PREDEL_VALIDATION => FALSE,
P_PERSON_ORG_MANAGER_WARNING => x_man_message
);
dbms_output.put_line('Success'||x_person_id);
end loop;
--commit;
END;
select DISTINCT PERSON_ID from per_all_people_f --where person_id = 118
Send Email Attachment through Oracle Package
DECLARE
v_file_handle UTL_FILE.FILE_TYPE;
v_email_server VARCHAR2(100) := 'xxxxxx';
v_conn UTL_SMTP.CONNECTION;
v_port NUMBER := 25;
v_reply UTL_SMTP.REPLY;
v_msg VARCHAR2(32767);
v_line VARCHAR2(1000);
v_message VARCHAR2(1000) ;
b_connected BOOLEAN := FALSE;
v_sender VARCHAR2(50) := 'temp-sandeep@xxxx.ae' ;
v_strt_day t_wk.wk_strt_day_d%TYPE;
v_end_day t_wk.wk_strt_day_d%TYPE;
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
RECPT VARCHAR2(255) := 'temp-sandeep@xxxx.ae';
SLP PLS_INTEGER :=
300;
pdirpath varchar2(50) := 'c:\sample\report';
pfilename varchar2(50) := 'test.xls';
BEGIN
p_stat := 0;
/***** Check if the file exists ****/
BEGIN
v_file_handle := UTL_FILE.FOPEN(pDirPath, pFileName, 'R'
);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
p_stat := 99;
RETURN;
WHEN OTHERS THEN
p_stat := 99;
RETURN;
END;
/***** Try to connect for three times, do sleep in between for 5
minutes *****/
FOR i IN 1..3
LOOP
BEGIN
--open the connection with the smtp server and
--do the handshake
v_conn:= UTL_SMTP.OPEN_CONNECTION(
v_email_server, v_port );
v_reply :=UTL_SMTP.HELO( v_conn, v_email_server
);
IF 250 = v_reply.code THEN
b_connected := TRUE;
EXIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOCK.SLEEP (SLP);
END;
END LOOP;
IF b_connected = FALSE THEN
p_stat := 99;
RETURN;
END IF;
v_reply := UTL_SMTP.MAIL(v_conn, v_sender);
IF 250 != v_reply.code THEN
p_stat := 99;
RETURN;
END IF;
v_reply := UTL_SMTP.RCPT(v_conn, RECPT);
IF 250 != v_reply.code THEN
p_stat := 99;
RETURN;
END IF;
UTL_SMTP.OPEN_DATA ( v_conn);
v_message := 'Sample Email This is an auto generated mail.
Please do not reply to this mail.'||chr(10);
v_msg := 'Date: '
|| TO_CHAR( SYSDATE, 'Mon DD yy hh24:mi:ss' ) || CRLF
|| 'From: '
|| v_sender || CRLF
|| 'Subject: '
|| 'Sample file' || CRLF
|| 'To: ' || RECPT || CRLF
|| 'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"' || CRLF ||'' || CRLF ||v_message||
CRLF ||'' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain;
name="v_message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="v_message.txt"' ||
CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF || '' || CRLF ||
v_message || CRLF || CRLF || CRLF;
UTL_SMTP.WRITE_DATA(v_conn,v_msg);
/***** Prepare the attachment to be sent *****/
v_Msg := CRLF || '--DMW.Boundary.605592468' || CRLF
|| 'Content-Type:
application/octet-stream; name="'
|| pFileName || '"' || CRLF
|| 'Content-Disposition: attachment; filename="'
|| pFileName || '"' || CRLF
|| 'Content-Transfer-Encoding: 7bit' || CRLF || CRLF;
UTL_SMTP.WRITE_DATA (v_conn, v_msg );
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file_handle, v_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
v_msg := '*** truncated ***' || CRLF;
v_msg := v_line || CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
END LOOP;
UTL_FILE.FCLOSE(v_file_handle);
v_msg := CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
v_msg := CRLF || '--DMW.Boundary.605592468--' || CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
UTL_SMTP.CLOSE_DATA( v_conn );
UTL_SMTP.QUIT( v_conn );
EXCEPTION
WHEN OTHERS THEN
p_stat := 99;
END;
v_file_handle UTL_FILE.FILE_TYPE;
v_email_server VARCHAR2(100) := 'xxxxxx';
v_conn UTL_SMTP.CONNECTION;
v_port NUMBER := 25;
v_reply UTL_SMTP.REPLY;
v_msg VARCHAR2(32767);
v_line VARCHAR2(1000);
v_message VARCHAR2(1000) ;
b_connected BOOLEAN := FALSE;
v_sender VARCHAR2(50) := 'temp-sandeep@xxxx.ae' ;
v_strt_day t_wk.wk_strt_day_d%TYPE;
v_end_day t_wk.wk_strt_day_d%TYPE;
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
RECPT VARCHAR2(255) := 'temp-sandeep@xxxx.ae';
SLP PLS_INTEGER :=
300;
pdirpath varchar2(50) := 'c:\sample\report';
pfilename varchar2(50) := 'test.xls';
BEGIN
p_stat := 0;
/***** Check if the file exists ****/
BEGIN
v_file_handle := UTL_FILE.FOPEN(pDirPath, pFileName, 'R'
);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
p_stat := 99;
RETURN;
WHEN OTHERS THEN
p_stat := 99;
RETURN;
END;
/***** Try to connect for three times, do sleep in between for 5
minutes *****/
FOR i IN 1..3
LOOP
BEGIN
--open the connection with the smtp server and
--do the handshake
v_conn:= UTL_SMTP.OPEN_CONNECTION(
v_email_server, v_port );
v_reply :=UTL_SMTP.HELO( v_conn, v_email_server
);
IF 250 = v_reply.code THEN
b_connected := TRUE;
EXIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOCK.SLEEP (SLP);
END;
END LOOP;
IF b_connected = FALSE THEN
p_stat := 99;
RETURN;
END IF;
v_reply := UTL_SMTP.MAIL(v_conn, v_sender);
IF 250 != v_reply.code THEN
p_stat := 99;
RETURN;
END IF;
v_reply := UTL_SMTP.RCPT(v_conn, RECPT);
IF 250 != v_reply.code THEN
p_stat := 99;
RETURN;
END IF;
UTL_SMTP.OPEN_DATA ( v_conn);
v_message := 'Sample Email This is an auto generated mail.
Please do not reply to this mail.'||chr(10);
v_msg := 'Date: '
|| TO_CHAR( SYSDATE, 'Mon DD yy hh24:mi:ss' ) || CRLF
|| 'From: '
|| v_sender || CRLF
|| 'Subject: '
|| 'Sample file' || CRLF
|| 'To: ' || RECPT || CRLF
|| 'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"' || CRLF ||'' || CRLF ||v_message||
CRLF ||'' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain;
name="v_message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="v_message.txt"' ||
CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF || '' || CRLF ||
v_message || CRLF || CRLF || CRLF;
UTL_SMTP.WRITE_DATA(v_conn,v_msg);
/***** Prepare the attachment to be sent *****/
v_Msg := CRLF || '--DMW.Boundary.605592468' || CRLF
|| 'Content-Type:
application/octet-stream; name="'
|| pFileName || '"' || CRLF
|| 'Content-Disposition: attachment; filename="'
|| pFileName || '"' || CRLF
|| 'Content-Transfer-Encoding: 7bit' || CRLF || CRLF;
UTL_SMTP.WRITE_DATA (v_conn, v_msg );
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file_handle, v_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
v_msg := '*** truncated ***' || CRLF;
v_msg := v_line || CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
END LOOP;
UTL_FILE.FCLOSE(v_file_handle);
v_msg := CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
v_msg := CRLF || '--DMW.Boundary.605592468--' || CRLF;
UTL_SMTP.WRITE_DATA ( v_conn, v_msg );
UTL_SMTP.CLOSE_DATA( v_conn );
UTL_SMTP.QUIT( v_conn );
EXCEPTION
WHEN OTHERS THEN
p_stat := 99;
END;
How to Insert Blob data(image, video) into oracle BLOB size
How to Insert Blob data(image, video) into oracle BLOB size
In this post it is shown how I can insert Blob data link image video into oracle database and also how I can determine the size of the BLOB data from oracle.
1)Create Directory Where BLOB resides.
create or replace directory temp as '/oradata2';
2)Grant read permission to the user who work with this directory.
grant read on directory temp to arju;
3)Create the Table which holds lob object.
-- the storage table for the image file
CREATE TABLE pdm (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file
4)Create the procedure that insert BLOB objects.
-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);
-- insert a NULL record to lock
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE pdm
SET iblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- close file
dbms_lob.fileclose(src_file);
END load_file;
/
5)Execute the Procedure.
SQL> exec load_file('TEMP','This is Image','tritha7.png');
PL/SQL procedure successfully completed.
6) From OS see the BLOB size.
SQL> !ls -l /oradata2/tritha7.png
-rwxr-xr-x 1 oracle oinstall 21150 Jun 9 01:55 /oradata2/tritha7.png
7)From Oracle Determine Blob size.
1 declare
2 a blob;
3 begin
4 select iblob into a from pdm;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
21150
PL/SQL procedure successfully completed.
In this post it is shown how I can insert Blob data link image video into oracle database and also how I can determine the size of the BLOB data from oracle.
1)Create Directory Where BLOB resides.
create or replace directory temp as '/oradata2';
2)Grant read permission to the user who work with this directory.
grant read on directory temp to arju;
3)Create the Table which holds lob object.
-- the storage table for the image file
CREATE TABLE pdm (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file
4)Create the procedure that insert BLOB objects.
-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);
-- insert a NULL record to lock
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE pdm
SET iblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- close file
dbms_lob.fileclose(src_file);
END load_file;
/
5)Execute the Procedure.
SQL> exec load_file('TEMP','This is Image','tritha7.png');
PL/SQL procedure successfully completed.
6) From OS see the BLOB size.
SQL> !ls -l /oradata2/tritha7.png
-rwxr-xr-x 1 oracle oinstall 21150 Jun 9 01:55 /oradata2/tritha7.png
7)From Oracle Determine Blob size.
1 declare
2 a blob;
3 begin
4 select iblob into a from pdm;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
21150
PL/SQL procedure successfully completed.
Active Customer List
SELECT party.party_name customer_name, acnt.account_number customer_number,
SUBSTRB (loc.address1, 1, 200) address_line_1,
SUBSTRB (loc.city, 1, 15) city
FROM hz_cust_accounts acnt,
hz_parties party,
hz_cust_acct_sites_all site,
hz_cust_site_uses_all uses,
hz_party_sites ps,
hz_locations loc
WHERE acnt.status = 'A'
AND party.party_id = acnt.party_id
AND acnt.cust_account_id = site.cust_account_id(+)
AND site.status(+) = 'A'
AND site.cust_acct_site_id = uses.cust_acct_site_id(+)
AND ps.party_site_id(+) = site.party_site_id
AND uses.site_use_code(+) = 'BILL_TO'
AND loc.location_id(+) = ps.location_id
SUBSTRB (loc.address1, 1, 200) address_line_1,
SUBSTRB (loc.city, 1, 15) city
FROM hz_cust_accounts acnt,
hz_parties party,
hz_cust_acct_sites_all site,
hz_cust_site_uses_all uses,
hz_party_sites ps,
hz_locations loc
WHERE acnt.status = 'A'
AND party.party_id = acnt.party_id
AND acnt.cust_account_id = site.cust_account_id(+)
AND site.status(+) = 'A'
AND site.cust_acct_site_id = uses.cust_acct_site_id(+)
AND ps.party_site_id(+) = site.party_site_id
AND uses.site_use_code(+) = 'BILL_TO'
AND loc.location_id(+) = ps.location_id
Connecting SQL Server Database from Oracle Applications database
Connecting SQL Server Database from Oracle Applications database:
1. Database of the SQL Server 2005(Test)
2. Database of Oracle Applications - Oracle 10.2 (UATDB)
Step 1: First Create the ODBC System DSN on UATDB.
Step 3: Edit the TNSNAMES.ORA file.
Step 4: Edit the Listener.ora:
#
# $Header: ad8ilsnr.ora 120.1 2006/06/07 05:52:02 sbandla noship $
#
# ###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and
# overwritten. If you were instructed to edit this file, or if you are not
# able to use the settings created by AutoConfig, refer to Metalink Note
# 387859.1 for assistance.
#
# ###############################################################
#
#
# Net8 definition for Database listener
#
JANC =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCJANC))
(ADDRESS= (PROTOCOL= TCP)(Host=****)(Port= ****))
)
SID_LIST_JANC =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= D:\DB\****\db\tech_st\10.2.0)
(SID_NAME = ****)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\DB\****\db\tech_st\10.2.0)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=HSODBC)
(ORACLE_HOME=D:\DB\**** \db\tech_st\10.2.0)
(PROGRAM=hsodbc)
)
)
STARTUP_WAIT_TIME_JANC = 0
CONNECT_TIMEOUT_JANC = 10
TRACE_LEVEL_JANC = OFF
LOG_DIRECTORY_JANC = D:\DB\****\db\tech_st\10.2.0/network/admin
LOG_FILE_JANC = JANC
TRACE_DIRECTORY_JANC = D:\DB\****\db\tech_st\10.2.0/network/admin
TRACE_FILE_JANC = JANC
ADMIN_RESTRICTIONS_JANC = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_JANC = OFF
IFILE=D:\DB\****\db\tech_st\10.2.0\network\admin\JANC_uatdb\listener_ifile.ora
Step 5 : Login to **** Database using APPS user;
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\appluat>sqlplus / as sysdba;
SQL*Plus: Release 10.1.0.5.0 - Production on Sun May 9 08:57:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn apps/xxxxxx@**** as sysdba;
Connected.
SQL> select name from v$database;
NAME
---------
****
SQL> create public database link HSODBC connect to "sa" identified by "password"
using 'hsodbc';
Database link created.
SQL> select count(*) from employees@hsodbc;
COUNT(*)
----------
30378
You did it! J
Step 6 : Now create view in SQL Server database. Make all the column names as capital:
create view [dbo].[****] as select query for sql server database
Step 7 : Create synonym for the view created in SQL Server:
Create synonym **** for ****@HSBDBC;
Cherrrrrrrrrrrrrrrrrrrrrrrrssssssssssssssssssssssss….
J
AP Invoice Cancel API
DECLARE
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
l_user_id NUMBER := 2083;
l_resp_id NUMBER := 20639;
l_appl_id NUMBER := 200;
CURSOR C_Inv_Det is
SELECT distinct aia.*
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.org_id = 308
AND aia.invoice_num = '300040823'
AND aia.payment_status_flag = 'N'
AND NVL(aila.cancelled_flag, 'N') <> 'Y';
BEGIN
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
FOR l_inv_rec IN C_Inv_Det LOOP
mo_global.init ('SQLAP');
mo_global.set_policy_context ('S', l_inv_rec.org_id);
DBMS_OUTPUT.put_line ('Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: ' l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line ('**************************************************************');
l_boolean := ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
P_Token => l_token,
p_calling_sequence => NULL);
DBMS_OUTPUT.put_line ('l_message_name => ' l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' l_tax_amount);
DBMS_OUTPUT.put_line ('l_temp_cancelled_amount => ' l_temp_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' l_last_update_date);
DBMS_OUTPUT.put_line ('P_original_prepayment_amount => ' l_original_prepayment_amount);
DBMS_OUTPUT.put_line ('l_pay_curr_invoice_amount => ' l_pay_curr_invoice_amount);
IF l_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice => ' l_inv_rec.invoice_num);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice => ' l_inv_rec.invoice_num);
ROLLBACK;
END IF;
END LOOP;
END;
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
l_user_id NUMBER := 2083;
l_resp_id NUMBER := 20639;
l_appl_id NUMBER := 200;
CURSOR C_Inv_Det is
SELECT distinct aia.*
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.org_id = 308
AND aia.invoice_num = '300040823'
AND aia.payment_status_flag = 'N'
AND NVL(aila.cancelled_flag, 'N') <> 'Y';
BEGIN
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
FOR l_inv_rec IN C_Inv_Det LOOP
mo_global.init ('SQLAP');
mo_global.set_policy_context ('S', l_inv_rec.org_id);
DBMS_OUTPUT.put_line ('Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: ' l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line ('**************************************************************');
l_boolean := ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
P_Token => l_token,
p_calling_sequence => NULL);
DBMS_OUTPUT.put_line ('l_message_name => ' l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' l_tax_amount);
DBMS_OUTPUT.put_line ('l_temp_cancelled_amount => ' l_temp_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' l_last_update_date);
DBMS_OUTPUT.put_line ('P_original_prepayment_amount => ' l_original_prepayment_amount);
DBMS_OUTPUT.put_line ('l_pay_curr_invoice_amount => ' l_pay_curr_invoice_amount);
IF l_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice => ' l_inv_rec.invoice_num);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice => ' l_inv_rec.invoice_num);
ROLLBACK;
END IF;
END LOOP;
END;
Sunday, November 14, 2010
Script to compile all collection and classification packages
SET verify off
whenever sqlerror EXIT failure ROLLBACK;
REM Added for ARU db drv auto generation
REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
REM $Header: cncolgen.sql 115.1.1157.1 2002/03/06 22:54:22 pkm ship $
declare
l_errbuf VARCHAR2(1800);
l_retcode NUMBER;
l_debug_pipe VARCHAR2(2000);
l_debug_level NUMBER;
l_orginal_org VARCHAR2(100);
l_status VARCHAR2(100);
cursor maps is
select tm.table_map_id,
tm.org_id,
tm.module_id,
mo.module_status
from cn_table_maps_all tm,
cn_modules_all_b mo
where tm.module_id = mo.module_id and
tm.org_id = mo.org_id and
mo.module_status = 'GENERATED'
order by tm.table_map_id, tm.org_id, tm.module_id;
cursor rules is
select ruleset_id, org_id
from cn_rulesets_all_b
where org_id <> -3113 ;
begin
dbms_application_info.read_client_info(l_orginal_org);
-- ==============================
-- Regenerate Collection packages
-- ==============================
for map in maps loop
-- dbms_output.put_line('map.table_map_id = ' || map.table_map_id || ' map.org_id = '|| map.org_id);
dbms_application_info.set_client_info(map.org_id);
-- Generate Collection code. Code is stored in CN_SOURCE.
cn_collection_gen.collection_pkg(l_debug_pipe,
l_debug_level,
map.table_map_id);
-- Install the collection test package.
cn_collection_gen.collection_install(l_errbuf,
l_retcode,
map.table_map_id,
'Y');
-- Install the collection package.
cn_collection_gen.collection_install(l_errbuf,
l_retcode,
map.table_map_id);
end loop;
-- ========================================
-- Regenerate Classification Rules packages
-- ========================================
for rule in rules loop
-- dbms_output.put_line('rule.ruleset_id = ' || rule.ruleset_id || ' rule.org_id = '|| rule.org_id);
dbms_application_info.set_client_info(rule.org_id);
cn_rulesets_pkg.sync_ruleset(rule.ruleset_id,
l_status);
IF l_status <> 'UNSYNC' THEN
cn_classification_gen.Classification_Install(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
x_ruleset_id => rule.ruleset_id);
END IF;
end loop;
dbms_application_info.set_client_info(l_orginal_org);
exception
when others then
dbms_application_info.set_client_info(l_orginal_org);
end;
/
commit;
whenever sqlerror EXIT failure ROLLBACK;
REM Added for ARU db drv auto generation
REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
REM $Header: cncolgen.sql 115.1.1157.1 2002/03/06 22:54:22 pkm ship $
declare
l_errbuf VARCHAR2(1800);
l_retcode NUMBER;
l_debug_pipe VARCHAR2(2000);
l_debug_level NUMBER;
l_orginal_org VARCHAR2(100);
l_status VARCHAR2(100);
cursor maps is
select tm.table_map_id,
tm.org_id,
tm.module_id,
mo.module_status
from cn_table_maps_all tm,
cn_modules_all_b mo
where tm.module_id = mo.module_id and
tm.org_id = mo.org_id and
mo.module_status = 'GENERATED'
order by tm.table_map_id, tm.org_id, tm.module_id;
cursor rules is
select ruleset_id, org_id
from cn_rulesets_all_b
where org_id <> -3113 ;
begin
dbms_application_info.read_client_info(l_orginal_org);
-- ==============================
-- Regenerate Collection packages
-- ==============================
for map in maps loop
-- dbms_output.put_line('map.table_map_id = ' || map.table_map_id || ' map.org_id = '|| map.org_id);
dbms_application_info.set_client_info(map.org_id);
-- Generate Collection code. Code is stored in CN_SOURCE.
cn_collection_gen.collection_pkg(l_debug_pipe,
l_debug_level,
map.table_map_id);
-- Install the collection test package.
cn_collection_gen.collection_install(l_errbuf,
l_retcode,
map.table_map_id,
'Y');
-- Install the collection package.
cn_collection_gen.collection_install(l_errbuf,
l_retcode,
map.table_map_id);
end loop;
-- ========================================
-- Regenerate Classification Rules packages
-- ========================================
for rule in rules loop
-- dbms_output.put_line('rule.ruleset_id = ' || rule.ruleset_id || ' rule.org_id = '|| rule.org_id);
dbms_application_info.set_client_info(rule.org_id);
cn_rulesets_pkg.sync_ruleset(rule.ruleset_id,
l_status);
IF l_status <> 'UNSYNC' THEN
cn_classification_gen.Classification_Install(
x_errbuf => l_errbuf,
x_retcode => l_retcode,
x_ruleset_id => rule.ruleset_id);
END IF;
end loop;
dbms_application_info.set_client_info(l_orginal_org);
exception
when others then
dbms_application_info.set_client_info(l_orginal_org);
end;
/
commit;
Query to Find Responsibility from the Concurrent Program
SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID =FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXX%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US'
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID =FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXX%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US'
Create PDCs
DECLARE
l_return_status VARCHAR2(500);
l_msg_count NUMBER;
l_msg_data VARCHAR2(500);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2(500);
l_mesg VARCHAR2(500);
p_count number;
p_receipt_number_1 varchar2(50);
P_attribute_rec_type_1 AR_RECEIPT_API_PUB.attribute_rec_type;
--p_currency_code varchar2(20);
cursor c1
is
select rowid, a.*
from xx_err_2 a;
-- where ACCOUNT_ID IS NOT NULL
-- and SEQ_NO = 1750;
--and receipt_number not in ('130FV');
BEGIN
FND_global.APPS_INITIALIZE(1090,20678,222);
MO_GLOBAL.SET_POLICY_CONTEXT('S', 82);
--mo_global.SET_ORG_ACCESS('XXXX OU');
for z in
c1 loop
dbms_output.put_line('Start');
l_return_status:=null;
p_receipt_number_1:=z.RECEIPT_NUMBER;
--P_attribute_rec_type_1.attribute_category := 'PDC'; --i.attribute_category;
--P_attribute_rec_type_1.attribute1 := z.REFF_NUMBER;
--P_attribute_rec_type_1.attribute2 := z.attribute2;
--arp_standard.enable_debug;
--arp_standard.enable_file_debug('C:\re', 're');
AR_RECEIPT_API_PUB.create_cash
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => z.RECEIPT_CURRENCY,
p_amount => z.RECEIPT_AMOUNT,
p_receipt_number => z.RECEIPT_NUMBER,
p_doc_sequence_value => NULL,
p_receipt_date => TO_DATE(z.RECEIPT_DATE,'DD/MM/RRRR'),
p_gl_date => z.GL_DATE,
--p_customer_number => z.CUSTOMER_NUMBER,
p_customer_id => z.ACCOUNT_ID,
p_receipt_method_id => 1040,
p_cr_id => l_cash_receipt_id,
--p_attribute_rec => P_attribute_rec_type_1,
p_maturity_date => to_date(z.MARUTIRY_DATE,'DD/MM/RRRR'),
p_customer_receipt_reference => replace(z.REFF_NUMBER,' ',''),
p_comments => z.COMMENT1,
p_org_id => 82 );
dbms_output.put_line('Status ' || l_return_status);
dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );
dbms_output.put_line('Message count ' || l_msg_count);
if l_return_status = 'E' then
update xx_err_2
set account_number= substr(l_return_status||'-'||l_msg_data,1,50)
where rowid=z.rowid;
commit;
end if;
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
dbms_output.put_line('RECEIPT NUMBER' || p_receipt_number_1 );
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || p_count ||'.'||l_msg_data);
END LOOP;
END IF;
end loop;
arp_standard.disable_debug;
END;
l_return_status VARCHAR2(500);
l_msg_count NUMBER;
l_msg_data VARCHAR2(500);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2(500);
l_mesg VARCHAR2(500);
p_count number;
p_receipt_number_1 varchar2(50);
P_attribute_rec_type_1 AR_RECEIPT_API_PUB.attribute_rec_type;
--p_currency_code varchar2(20);
cursor c1
is
select rowid, a.*
from xx_err_2 a;
-- where ACCOUNT_ID IS NOT NULL
-- and SEQ_NO = 1750;
--and receipt_number not in ('130FV');
BEGIN
FND_global.APPS_INITIALIZE(1090,20678,222);
MO_GLOBAL.SET_POLICY_CONTEXT('S', 82);
--mo_global.SET_ORG_ACCESS('XXXX OU');
for z in
c1 loop
dbms_output.put_line('Start');
l_return_status:=null;
p_receipt_number_1:=z.RECEIPT_NUMBER;
--P_attribute_rec_type_1.attribute_category := 'PDC'; --i.attribute_category;
--P_attribute_rec_type_1.attribute1 := z.REFF_NUMBER;
--P_attribute_rec_type_1.attribute2 := z.attribute2;
--arp_standard.enable_debug;
--arp_standard.enable_file_debug('C:\re', 're');
AR_RECEIPT_API_PUB.create_cash
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => z.RECEIPT_CURRENCY,
p_amount => z.RECEIPT_AMOUNT,
p_receipt_number => z.RECEIPT_NUMBER,
p_doc_sequence_value => NULL,
p_receipt_date => TO_DATE(z.RECEIPT_DATE,'DD/MM/RRRR'),
p_gl_date => z.GL_DATE,
--p_customer_number => z.CUSTOMER_NUMBER,
p_customer_id => z.ACCOUNT_ID,
p_receipt_method_id => 1040,
p_cr_id => l_cash_receipt_id,
--p_attribute_rec => P_attribute_rec_type_1,
p_maturity_date => to_date(z.MARUTIRY_DATE,'DD/MM/RRRR'),
p_customer_receipt_reference => replace(z.REFF_NUMBER,' ',''),
p_comments => z.COMMENT1,
p_org_id => 82 );
dbms_output.put_line('Status ' || l_return_status);
dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );
dbms_output.put_line('Message count ' || l_msg_count);
if l_return_status = 'E' then
update xx_err_2
set account_number= substr(l_return_status||'-'||l_msg_data,1,50)
where rowid=z.rowid;
commit;
end if;
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '||l_msg_data);
dbms_output.put_line('RECEIPT NUMBER' || p_receipt_number_1 );
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || p_count ||'.'||l_msg_data);
END LOOP;
END IF;
end loop;
arp_standard.disable_debug;
END;
On Hand Quantities Query
set serveroutput on
prompt Enter Organization_id
accept org_id
prompt Enter Inventory_item_id
accept item_id
DECLARE
L_api_return_status VARCHAR2(1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
BEGIN
apps.inv_quantity_tree_grp.clear_quantity_cache;
dbms_output.put_line('Transaction Mode');
apps.INV_Quantity_Tree_PUB.Query_Quantities (
p_api_version_number => 1.0
, p_init_msg_lst => apps.fnd_api.g_false
, x_return_status => L_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => &org_id
, p_inventory_item_id => &item_id
, p_tree_mode => apps.INV_Quantity_Tree_PUB.g_transaction_mode
, p_onhand_source => 3
, p_is_revision_control=> false
, p_is_lot_control => FALSE
, p_is_serial_control => FALSE
, p_revision => NULL
, p_lot_number => NULL
, p_subinventory_code => '&Subinventory'
, p_locator_id => NULL
, x_qoh => l_qty_oh
, x_rqoh => l_qty_res_oh
, x_qr => l_qty_res
, x_qs => l_qty_sug
, x_att => l_qty_att
, x_atr => l_qty_atr );
dbms_output.put_line('Quantity on hand :'||to_char(l_qty_oh));
dbms_output.put_line('Quantity res oh :'||to_char(l_qty_res_oh));
dbms_output.put_line('Quantity res :'||to_char(l_qty_res));
dbms_output.put_line('Quantity sug :'||to_char(l_qty_sug));
dbms_output.put_line('Quantity ATT :'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR :'||to_char(l_qty_atr));
end;
prompt Enter Organization_id
accept org_id
prompt Enter Inventory_item_id
accept item_id
DECLARE
L_api_return_status VARCHAR2(1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
BEGIN
apps.inv_quantity_tree_grp.clear_quantity_cache;
dbms_output.put_line('Transaction Mode');
apps.INV_Quantity_Tree_PUB.Query_Quantities (
p_api_version_number => 1.0
, p_init_msg_lst => apps.fnd_api.g_false
, x_return_status => L_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => &org_id
, p_inventory_item_id => &item_id
, p_tree_mode => apps.INV_Quantity_Tree_PUB.g_transaction_mode
, p_onhand_source => 3
, p_is_revision_control=> false
, p_is_lot_control => FALSE
, p_is_serial_control => FALSE
, p_revision => NULL
, p_lot_number => NULL
, p_subinventory_code => '&Subinventory'
, p_locator_id => NULL
, x_qoh => l_qty_oh
, x_rqoh => l_qty_res_oh
, x_qr => l_qty_res
, x_qs => l_qty_sug
, x_att => l_qty_att
, x_atr => l_qty_atr );
dbms_output.put_line('Quantity on hand :'||to_char(l_qty_oh));
dbms_output.put_line('Quantity res oh :'||to_char(l_qty_res_oh));
dbms_output.put_line('Quantity res :'||to_char(l_qty_res));
dbms_output.put_line('Quantity sug :'||to_char(l_qty_sug));
dbms_output.put_line('Quantity ATT :'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR :'||to_char(l_qty_atr));
end;
Need to Ship Quantity Query
SELECT DISTINCT ooh.order_number, wdd.source_header_type_name,
wdd.cust_po_number, wdd.organization_id,
wdd.src_requested_quantity, wdd.requested_quantity,
wdd.released_status, flv.meaning,
wdd.oe_interfaced_flag,
wdd.inv_interfaced_flag, wdd.mvt_stat_status,
wdd.creation_date, wdd.org_id, wdd.inventory_item_id,
wnd.status_code,flv1.meaning,flv1.description, mtrl.pick_slip_date, mtrl.primary_quantity,
mtrh.request_number, ool.flow_status_code,
ooh.flow_status_code,mil.segment5 Position
FROM wsh.wsh_trips wt,
wsh.wsh_trip_stops wtt,
wsh.wsh_delivery_legs wdl,
wsh.wsh_new_deliveries wnd,
wsh.wsh_delivery_assignments wda,
wsh.wsh_delivery_details wdd,
ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
inv.mtl_txn_request_lines mtrl,
inv.mtl_txn_request_headers mtrh,
apps.fnd_lookup_values flv,
apps.fnd_lookup_values flv1,
inv.mtl_material_transactions mmt,
inv.mtl_item_locations MIL
WHERE wt.trip_id(+) = wtt.trip_id
AND wtt.stop_id(+) = wdl.pick_up_stop_id
AND wnd.delivery_id = wdl.delivery_id(+)
AND wnd.delivery_id(+) = wda.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND ooh.header_id = wdd.source_header_id
AND ooh.header_id = ool.header_id(+)
AND ool.line_id = mtrl.txn_source_line_id(+)
AND ool.line_id = wdd.source_line_id(+)
AND mtrh.header_id(+) = mtrl.header_id
--and flv.view_application_id =665
and wdd.released_status = flv.lookup_code
and flv.lookup_type='PICK_STATUS'
and flv1.view_application_id =665
and wnd.status_code = flv1.lookup_code
and flv1.lookup_type='DELIVERY_STATUS'
AND wdd.source_code = 'OE'
--AND wt.name = --'142310'
AND mmt.trx_source_line_id = ool.line_id
and mmt.organization_id = ool.ship_from_org_id
AND mmt.locator_id = mil.inventory_location_id
And mmt.inventory_item_id = ool.inventory_item_id
AND mmt.transaction_quantity < 0
AND mmt.transaction_type_id = 52
And mmt.MOVE_ORDER_LINE_ID = mtrl.LINE_ID
AND wnd.name = '147190'
AND ooh.order_number = '&order_number'
SELECT distinct ooha.ORDER_NUMBER ORDER_NUMBER,wnd.delivery_id delivery_id,ooha.header_id header_id
FROM OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND
WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID(+)
AND OOLA.LINE_ID = WDD.SOURCE_LINE_ID
-- AND WDD.SOURCE_CODE = 'OE'
-- AND WDD.RELEASED_STATUS IN ('C', 'I', 'Y')
Select customer_trx_line_id ,rct.customer_trx_id ,rct.trx_number
From ra_customer_trx_lines_all rctl, ra_customer_trx_all rct
Where rct.customer_trx_id = rctl.customer_trx_id
and nvl(rct.interface_header_context,'X') in ('ORDER ENTRY','PROJECTS INVOICES')
and rct.org_id = 82
and rct.trx_date between to_date('01-JAN-2009', 'DD-MON-YYYY') and to_date('31-JAN-2009', 'DD-MON-YYYY')
and exists (select 1 from ra_cust_trx_line_gl_dist_all rctlgd where rctlgd.customer_trx_id = rct.customer_trx_id
and rctlgd.account_class ='REC' and rctlgd.latest_rec_flag ='Y' and rctlgd.org_id = rct.org_id
wdd.cust_po_number, wdd.organization_id,
wdd.src_requested_quantity, wdd.requested_quantity,
wdd.released_status, flv.meaning,
wdd.oe_interfaced_flag,
wdd.inv_interfaced_flag, wdd.mvt_stat_status,
wdd.creation_date, wdd.org_id, wdd.inventory_item_id,
wnd.status_code,flv1.meaning,flv1.description, mtrl.pick_slip_date, mtrl.primary_quantity,
mtrh.request_number, ool.flow_status_code,
ooh.flow_status_code,mil.segment5 Position
FROM wsh.wsh_trips wt,
wsh.wsh_trip_stops wtt,
wsh.wsh_delivery_legs wdl,
wsh.wsh_new_deliveries wnd,
wsh.wsh_delivery_assignments wda,
wsh.wsh_delivery_details wdd,
ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
inv.mtl_txn_request_lines mtrl,
inv.mtl_txn_request_headers mtrh,
apps.fnd_lookup_values flv,
apps.fnd_lookup_values flv1,
inv.mtl_material_transactions mmt,
inv.mtl_item_locations MIL
WHERE wt.trip_id(+) = wtt.trip_id
AND wtt.stop_id(+) = wdl.pick_up_stop_id
AND wnd.delivery_id = wdl.delivery_id(+)
AND wnd.delivery_id(+) = wda.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND ooh.header_id = wdd.source_header_id
AND ooh.header_id = ool.header_id(+)
AND ool.line_id = mtrl.txn_source_line_id(+)
AND ool.line_id = wdd.source_line_id(+)
AND mtrh.header_id(+) = mtrl.header_id
--and flv.view_application_id =665
and wdd.released_status = flv.lookup_code
and flv.lookup_type='PICK_STATUS'
and flv1.view_application_id =665
and wnd.status_code = flv1.lookup_code
and flv1.lookup_type='DELIVERY_STATUS'
AND wdd.source_code = 'OE'
--AND wt.name = --'142310'
AND mmt.trx_source_line_id = ool.line_id
and mmt.organization_id = ool.ship_from_org_id
AND mmt.locator_id = mil.inventory_location_id
And mmt.inventory_item_id = ool.inventory_item_id
AND mmt.transaction_quantity < 0
AND mmt.transaction_type_id = 52
And mmt.MOVE_ORDER_LINE_ID = mtrl.LINE_ID
AND wnd.name = '147190'
AND ooh.order_number = '&order_number'
SELECT distinct ooha.ORDER_NUMBER ORDER_NUMBER,wnd.delivery_id delivery_id,ooha.header_id header_id
FROM OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND
WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID(+)
AND OOLA.LINE_ID = WDD.SOURCE_LINE_ID
-- AND WDD.SOURCE_CODE = 'OE'
-- AND WDD.RELEASED_STATUS IN ('C', 'I', 'Y')
Select customer_trx_line_id ,rct.customer_trx_id ,rct.trx_number
From ra_customer_trx_lines_all rctl, ra_customer_trx_all rct
Where rct.customer_trx_id = rctl.customer_trx_id
and nvl(rct.interface_header_context,'X') in ('ORDER ENTRY','PROJECTS INVOICES')
and rct.org_id = 82
and rct.trx_date between to_date('01-JAN-2009', 'DD-MON-YYYY') and to_date('31-JAN-2009', 'DD-MON-YYYY')
and exists (select 1 from ra_cust_trx_line_gl_dist_all rctlgd where rctlgd.customer_trx_id = rct.customer_trx_id
and rctlgd.account_class ='REC' and rctlgd.latest_rec_flag ='Y' and rctlgd.org_id = rct.org_id
GL Interface
CREATE OR REPLACE PACKAGE XX_BOE_GL_INTERFACE_PKG
AUTHID CURRENT_USER
AS ;
PROCEDURE processdata
(p_org_id in number ) ;
END XX_BOE_GL_INTERFACE_PKG;
CREATE OR REPLACE PACKAGE BODY XX_BOE_GL_INTERFACE_PKG
AS
PROCEDURE processdata (p_org_id in number)
IS
CURSOR gl_cur_new
IS
SELECT *
FROM xxsoboe_headers_all
WHERE nvl(receive_flag,'N') = 'Y'
AND nvl(gl_interface_flag,'N') = 'N';
v_currency_code1 VARCHAR2 (15);
v_currency_code2 VARCHAR2 (15);
v_sob_id VARCHAR2 (15);
v_chart_of_account_id NUMBER ;
v_user_je_category_name VARCHAR2 (25);
v_period_name1 VARCHAR2 (15);
v_period_name2 VARCHAR2 (15);
v_segment1 VARCHAR2 (15);
v_segment2 VARCHAR2 (15);
v_segment3 VARCHAR2 (15);
v_segment4 VARCHAR2 (15);
v_segment5 VARCHAR2 (15);
v_segment6 VARCHAR2 (15);
v_segment7 VARCHAR2 (15);
v_error_reason VARCHAR2 (200);
v_sum_amount VARCHAR2 (10);
v_entered_dr VARCHAR2 (15);
v_entered_cr VARCHAR2 (15);
v_conv_type VARCHAR2 (100);
e_segments_vald EXCEPTION;
e_foreign_curr EXCEPTION;
e_dr_cr EXCEPTION;
BEGIN
FOR rec_cur_new IN gl_cur_new
LOOP
-- Initializing all variables
v_currency_code1 := '';
v_currency_code2 := '';
v_sob_id := '';
v_user_je_category_name := '';
v_period_name1 := '';
v_period_name2 := '';
v_segment1 := '';
v_segment2 := '';
v_segment3 := '';
v_segment4 := '';
v_segment5 := '';
v_segment6 := '';
v_segment7 := '';
v_error_reason := '';
v_sum_amount := '';
v_entered_dr := '';
v_entered_cr := '';
v_conv_type := '';
--THIS PLSQL BLOCK FINDS SET_OF_BOOKS ID AND CHART OF ACCOUNTS ID
BEGIN
select distinct OOD.set_of_books_id
, OOD.chart_of_accounts_id
INTO v_sob_id
, v_chart_of_account_id
from org_organization_definitions OOD
where OOD.operating_unit = p_org_id ;
fnd_file.put_line (fnd_file.LOG,'Set of Books and Chart of account validated');
exception
when OTHERS then
v_err_msg := substr(sqlerrm,1,60);
fnd_file.put_line (fnd_file.LOG,'Error while validatating SOB and COA - '||v_err_msg);
p_message := 'Error while validatating SOB and COA - '||v_err_msg;
END;
--END OF SOB AND COA VALIDATIONS
-- THIS PLSQL BLOCK VALIDATES THE CURRENCY CODE
BEGIN
SELECT distinct currency_code
INTO v_currency_code
FROM apps.gl_sets_of_books
WHERE set_of_books_id = v_sob_id;
fnd_file.put_line (fnd_file.LOG,'Currency Code Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Currency Code. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF CURRENCY CODE VALIDATIONS.
-- END OF SET OF BOOKS VALIDATIONS.
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME
BEGIN
SELECT DISTINCT period_name
INTO v_period_name
FROM apps.gl_periods
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
fnd_file.put_line(fnd_file.LOG,'Period Name Validated');
EXCEPTION
WHEN OTHERS then
p_message :=
' Error while validating Period Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF PERIOD NAME VALIDATION
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME STATUSES
BEGIN
SELECT period_name
INTO v_period_status
FROM apps.gl_period_statuses
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
AND set_of_books_id = v_sob_id
AND application_id = fnd_global.resp_appl_id
AND closing_status IN ('O');
fnd_file.put_line(fnd_file.LOG,'Period Name Statuses Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Period Status. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF VALIDATING PERIOD NAME STATUSES
-- THIS PLSQL BLOCK VALIDATES THE USER JE CATEGORY NAME
BEGIN
SELECT user_je_category_name
INTO v_user_je_category_name
FROM gl_je_categories
WHERE upper(user_je_category_name) = 'ACCRUAL';
fnd_file.put_line(fnd_file.LOG,'User Category Name Validated');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
p_message := ' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
END;
-- END OF USER JE CATEGORY NAME
-- THIS PLSQL BLOCK VALIDATES THE EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
BEGIN
select nvl(custom_duty_shipment_cost,0)
into v_entered_dr
from xx_boe_document_cost_v
where document_number = rec_cur_new.document_number;
EXCEPTION
WHEN others then
p_message :=
' Error while validating Dr & Cr Amount in Single Line. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line(fnd_file.LOG,p_message);
END;
IF rec_cur_new.trans_type = 'PO' then
begin
select distinct HCA.absorption_acct_ccid
into ln_code_combination_id
from hl_cst_comp_mst HCM
, hl_cst_comp_mst_acct HCA
where HCM.component_id = HCA.component_id
AND upper(HCM.component_name) = 'CUSTOMS DUTY';
exception
when others then
p_message := 'Please check Custom Duty Account setup in Landed Cost');
fnd_file.put_line(fnd_file.LOG,p_message);
END;
ELSE
begin
NULL;
EXCEPTION
END;
END IF;
-- END OF VALIDATING EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
begin
select ledger_id
into ln_ledger_id
from gl_ledgers
where ledger_category_code = 'PRIMARY'
exception
when others then
p_message := 'Please check Primary Legder setup');
fnd_file.put_line(fnd_file.LOG,p_message);
end;
-- END OF ACCOUNTING FLEXFIELD SEGMENTS
-- INSERTING DATA INTO INTERFACE TABLE
INSERT INTO GL_INTERFACE ( status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
chart_of_accounts_id,
functional_currency_code,
transaction_date,
period_name ,
ledger_id )
VALUES ( 'NEW',
v_sob_id
trunc(SYSDATE),
v_currency_code,
SYSDATE,
fnd_profile.value('USER_ID'),
'A',
,v_user_je_category_name
'Other',
ln_code_combination_id,
,v_entered_dr
0,
v_entered_dr,
0,
v_chart_of_account_id ,
v_currency_code,
SYSDATE,
v_period_name,
ln_ledger_id
)
UPDATE xxsoboe_headers_all
SET gl_interface_flag = 'Y'
, gl_interface_date = sysdate
WHERE doc_number = rec_cur_new.doc_number;
END LOOP;
END processdata;
/*-----------------------------------------------------------------------
runInterface
Called as a concurrent program - manages the import process
-----------------------------------------------------------------------*/
END dllgl_interface_pkg;
/
AUTHID CURRENT_USER
AS ;
PROCEDURE processdata
(p_org_id in number ) ;
END XX_BOE_GL_INTERFACE_PKG;
CREATE OR REPLACE PACKAGE BODY XX_BOE_GL_INTERFACE_PKG
AS
PROCEDURE processdata (p_org_id in number)
IS
CURSOR gl_cur_new
IS
SELECT *
FROM xxsoboe_headers_all
WHERE nvl(receive_flag,'N') = 'Y'
AND nvl(gl_interface_flag,'N') = 'N';
v_currency_code1 VARCHAR2 (15);
v_currency_code2 VARCHAR2 (15);
v_sob_id VARCHAR2 (15);
v_chart_of_account_id NUMBER ;
v_user_je_category_name VARCHAR2 (25);
v_period_name1 VARCHAR2 (15);
v_period_name2 VARCHAR2 (15);
v_segment1 VARCHAR2 (15);
v_segment2 VARCHAR2 (15);
v_segment3 VARCHAR2 (15);
v_segment4 VARCHAR2 (15);
v_segment5 VARCHAR2 (15);
v_segment6 VARCHAR2 (15);
v_segment7 VARCHAR2 (15);
v_error_reason VARCHAR2 (200);
v_sum_amount VARCHAR2 (10);
v_entered_dr VARCHAR2 (15);
v_entered_cr VARCHAR2 (15);
v_conv_type VARCHAR2 (100);
e_segments_vald EXCEPTION;
e_foreign_curr EXCEPTION;
e_dr_cr EXCEPTION;
BEGIN
FOR rec_cur_new IN gl_cur_new
LOOP
-- Initializing all variables
v_currency_code1 := '';
v_currency_code2 := '';
v_sob_id := '';
v_user_je_category_name := '';
v_period_name1 := '';
v_period_name2 := '';
v_segment1 := '';
v_segment2 := '';
v_segment3 := '';
v_segment4 := '';
v_segment5 := '';
v_segment6 := '';
v_segment7 := '';
v_error_reason := '';
v_sum_amount := '';
v_entered_dr := '';
v_entered_cr := '';
v_conv_type := '';
--THIS PLSQL BLOCK FINDS SET_OF_BOOKS ID AND CHART OF ACCOUNTS ID
BEGIN
select distinct OOD.set_of_books_id
, OOD.chart_of_accounts_id
INTO v_sob_id
, v_chart_of_account_id
from org_organization_definitions OOD
where OOD.operating_unit = p_org_id ;
fnd_file.put_line (fnd_file.LOG,'Set of Books and Chart of account validated');
exception
when OTHERS then
v_err_msg := substr(sqlerrm,1,60);
fnd_file.put_line (fnd_file.LOG,'Error while validatating SOB and COA - '||v_err_msg);
p_message := 'Error while validatating SOB and COA - '||v_err_msg;
END;
--END OF SOB AND COA VALIDATIONS
-- THIS PLSQL BLOCK VALIDATES THE CURRENCY CODE
BEGIN
SELECT distinct currency_code
INTO v_currency_code
FROM apps.gl_sets_of_books
WHERE set_of_books_id = v_sob_id;
fnd_file.put_line (fnd_file.LOG,'Currency Code Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Currency Code. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF CURRENCY CODE VALIDATIONS.
-- END OF SET OF BOOKS VALIDATIONS.
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME
BEGIN
SELECT DISTINCT period_name
INTO v_period_name
FROM apps.gl_periods
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
fnd_file.put_line(fnd_file.LOG,'Period Name Validated');
EXCEPTION
WHEN OTHERS then
p_message :=
' Error while validating Period Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF PERIOD NAME VALIDATION
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME STATUSES
BEGIN
SELECT period_name
INTO v_period_status
FROM apps.gl_period_statuses
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
AND set_of_books_id = v_sob_id
AND application_id = fnd_global.resp_appl_id
AND closing_status IN ('O');
fnd_file.put_line(fnd_file.LOG,'Period Name Statuses Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Period Status. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF VALIDATING PERIOD NAME STATUSES
-- THIS PLSQL BLOCK VALIDATES THE USER JE CATEGORY NAME
BEGIN
SELECT user_je_category_name
INTO v_user_je_category_name
FROM gl_je_categories
WHERE upper(user_je_category_name) = 'ACCRUAL';
fnd_file.put_line(fnd_file.LOG,'User Category Name Validated');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
p_message := ' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
END;
-- END OF USER JE CATEGORY NAME
-- THIS PLSQL BLOCK VALIDATES THE EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
BEGIN
select nvl(custom_duty_shipment_cost,0)
into v_entered_dr
from xx_boe_document_cost_v
where document_number = rec_cur_new.document_number;
EXCEPTION
WHEN others then
p_message :=
' Error while validating Dr & Cr Amount in Single Line. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line(fnd_file.LOG,p_message);
END;
IF rec_cur_new.trans_type = 'PO' then
begin
select distinct HCA.absorption_acct_ccid
into ln_code_combination_id
from hl_cst_comp_mst HCM
, hl_cst_comp_mst_acct HCA
where HCM.component_id = HCA.component_id
AND upper(HCM.component_name) = 'CUSTOMS DUTY';
exception
when others then
p_message := 'Please check Custom Duty Account setup in Landed Cost');
fnd_file.put_line(fnd_file.LOG,p_message);
END;
ELSE
begin
NULL;
EXCEPTION
END;
END IF;
-- END OF VALIDATING EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
begin
select ledger_id
into ln_ledger_id
from gl_ledgers
where ledger_category_code = 'PRIMARY'
exception
when others then
p_message := 'Please check Primary Legder setup');
fnd_file.put_line(fnd_file.LOG,p_message);
end;
-- END OF ACCOUNTING FLEXFIELD SEGMENTS
-- INSERTING DATA INTO INTERFACE TABLE
INSERT INTO GL_INTERFACE ( status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
chart_of_accounts_id,
functional_currency_code,
transaction_date,
period_name ,
ledger_id )
VALUES ( 'NEW',
v_sob_id
trunc(SYSDATE),
v_currency_code,
SYSDATE,
fnd_profile.value('USER_ID'),
'A',
,v_user_je_category_name
'Other',
ln_code_combination_id,
,v_entered_dr
0,
v_entered_dr,
0,
v_chart_of_account_id ,
v_currency_code,
SYSDATE,
v_period_name,
ln_ledger_id
)
UPDATE xxsoboe_headers_all
SET gl_interface_flag = 'Y'
, gl_interface_date = sysdate
WHERE doc_number = rec_cur_new.doc_number;
END LOOP;
END processdata;
/*-----------------------------------------------------------------------
runInterface
Called as a concurrent program - manages the import process
-----------------------------------------------------------------------*/
END dllgl_interface_pkg;
/
Subscribe to:
Posts (Atom)