Search This Blog

Saturday, November 20, 2010

List of POs - Audit Query

/* 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        

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

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;

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.

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

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;

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;

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'

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;

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;

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

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;
/