Oracle Workflow some imp scripts..

Oracle Workflow some imp scripts..

BEGIN --> purging the records from WF_ITEMS and WF_ITEM_ACTIVITY_STATUSES
WF_ENGINE.AbortProcess(ITEMTYPE,ITEMKEY,NULL,'FORCE');
WF_PURGE.Items(ITEMTYPE=>'XXXXX',ITEMKEY=>'XXXXX',ENDDATE=>SYSDATE,DOCOMMIT=>TRUE,FORCE=>TRUE);
END;

BEGIN              -- creates a record entry in WF_ITEMS TABLE
wf_engine.createprocess(itemtype => 'ITEMTYPE',itemkey => 'ITEMKEY',process => 'PROCESS_NAME');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;
       
BEGIN -- creates the action history in WF_ITEM_ACTIVITY_STATUSES table
wf_engine.startprocess(itemtype     => 'ITEMTYPE',itemkey      => 'ITEMKEY');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;

Regards,
Sal.

Oracle Quoting form Ship to information Query

Oracle Quoting form Ship to information Query...

SELECT hcsua.location address1, hl.address1 address2, hl.address2 address3,
  hl.address3 address4, hl.address4 city, hl.city state, hl.state postal_code,
   hl.postal_code county, hl.county province, hcsua.site_use_id country_code,
  hpsu.primary_per_type primary_per_type, terr.territory_short_name country,
  hcsua.site_use_code site_use_type, hp.party_name party_name,
  hps.party_site_id
FROM
 HZ_PARTIES hp,
 HZ_PARTY_SITES hps,
 HZ_LOCATIONS hl,
 FND_TERRITORIES_VL terr,
 HZ_CUST_SITE_USES_ALL hcsua,
 HZ_CUST_ACCT_SITES_ALL hcasa,
 HZ_PARTY_SITE_USES hpsu
WHERE hp.party_id = ?
AND hcasa.org_id = ?
AND hcsua.site_use_code = 'SHIP_TO'
AND hps.party_id = hp.party_id
AND hps.location_id = hl.location_id
AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
AND hcasa.party_site_id = hps.party_site_id
AND hl.country = terr.territory_code
AND hpsu.party_site_id = hps.party_site_id
AND hcsua.site_use_code = hpsu.site_use_type
AND hp.status = 'A'
AND hps.status = 'A'
AND hpsu.status='A'
ORDER BY hp.party_name, hl.address1, hl.address2, hl.address3, hl.address4

Regards,
Sal.

Oracle Quoting Customer information Query

Oracle Quoting Customer information Query..

SELECT hp.party_name, hp.party_number, ca.account_number, hp.party_type,
  hp.party_id, ca.cust_account_id
FROM
 HZ_PARTIES HP,
 HZ_CUST_ACCOUNTS CA
WHERE ( UPPER(PARTY_NAME) LIKE :1
 AND (PARTY_NAME LIKE :2))
 AND ( hp.party_type in ('PERSON','ORGANIZATION')
 AND hp.status = 'A'
 AND trunc(nvl(ca.account_activation_date,sysdate)) <= trunc(sysdate)
 AND trunc(nvl(ca.account_termination_date,sysdate)) >= trunc(sysdate)
 AND HP.party_id = CA.party_id
 AND ca.status = 'A'
 and rownum <= 5000 ) ORDER BY party_name,account_number

Regards,
Sal.

Oracle Quoting form Bill to information Query

Oracle Quoting form Bill to information Query..


SELECT hcsua.location address1, hl.address1 address2, hl.address2 address3,
  hl.address3 address4, hl.address4 city, hl.city state, hl.state postal_code,
   hl.postal_code county, hl.county province, hcsua.site_use_id country_code,
  hpsu.primary_per_type primary_per_type, terr.territory_short_name country,
  hcsua.site_use_code site_use_type, hp.party_name party_name,
  hps.party_site_id
FROM
 HZ_PARTIES hp,
 HZ_PARTY_SITES hps,
 HZ_LOCATIONS hl,
 FND_TERRITORIES_VL terr,
 HZ_CUST_SITE_USES_ALL hcsua,
 HZ_CUST_ACCT_SITES_ALL hcasa,
 HZ_PARTY_SITE_USES hpsu
WHERE hp.party_id = ?
  AND hcasa.org_id = ?
  AND hcsua.site_use_code = 'BILL_TO'
  AND hps.party_id = hp.party_id
  AND hps.location_id = hl.location_id
  AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
  AND hcasa.party_site_id = hps.party_site_id
  AND hl.country = terr.territory_code
  AND hpsu.party_site_id = hps.party_site_id
  AND hcsua.site_use_code = hpsu.site_use_type
  AND hp.status = 'A'
  AND hps.status = 'A'
  AND hpsu.status='A'
  ORDER BY hp.party_name, hl.address1,hl.address2, hl.address3, hl.address4

Regards,
Sal

Writing Text file into a table of BLOB column

BEGIN
Loading_BLOB_From_File('USER_DIR', '12345.txt',12345);END;
=================================================
Creating a Temp directory where the file can be placed..
=================================================

CREATE OR REPLACE DIRECTORY USER_DIR AS '/usr/tmp';

GRANT ALL on DIRECTORY USER_DIR TO PUBLIC; =================================================

CREATE OR REPLACE PROCEDURE Loading_BLOB_From_File ( P_DIR        IN VARCHAR2
                                    ,P_FILE_NAME IN VARCHAR2
                                    ,p_rowid  IN VARCHAR2)
AS
    dest_loc  BLOB;
    src_loc   BFILE := BFILENAME(P_DIR, P_FILE_NAME);
BEGIN
    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an text file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+
   DBMS_OUTPUT.put_line ('Before Updating');
  update XX_FILE_TABLE
  set TEXT = EMPTY_BLOB()
  where rowid = P_rowid
  RETURNING text INTO dest_loc;
DBMS_OUTPUT.put_line ('after Updating');
    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.put_line ('src_loc open');
    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_OUTPUT.put_line ('dest_loc open');
    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));
DBMS_OUTPUT.put_line ('dest_loc load file');
    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);
DBMS_OUTPUT.put_line ('close all db');
    COMMIT;
Exception
when others then
DBMS_OUTPUT.put_line (' Exception in Loading_BLOB_From_File'||SQLERRM);
END Loading_BLOB_From_File;
/


Thank you..
Sal..