SQL-Printer/Driver Queries

SELECT *
  FROM apps.FND_PRINTER_STYLES

;
SELECT PRINTER_DRIVER_NAME,
       USER_PRINTER_DRIVER_NAME,
       PRINTER_DRIVER_METHOD_CODE,
       SPOOL_FLAG,
       SRW_DRIVER,
       COMMAND_NAME,
       ARGUMENTS,
       INITIALIZATION,
       RESET
  FROM apps.FND_PRINTER_DRIVERS
 WHERE 1 = 1 AND printer_driver_name LIKE 'XXCNC%'

;
SELECT * FROM apps.FND_PRINTER_INFORMATION;



SELECT PRINTER_DRIVER_NAME,
       USER_PRINTER_DRIVER_NAME,
       PRINTER_DRIVER_METHOD_CODE,
       SPOOL_FLAG,
       SRW_DRIVER,
       COMMAND_NAME,
       ARGUMENTS,
       INITIALIZATION
  FROM apps.FND_PRINTER_DRIVERS
 WHERE PRINTER_DRIVER_NAME =
          (SELECT printer_driver
             FROM apps.FND_PRINTER_INFORMATION
           
            WHERE PRINTER_STYLE = (SELECT PRINT_STYLE
                                     FROM apps.FND_CONCURRENT_REQUESTS
                                    WHERE REQUEST_ID = :1));

SELECT * FROM apps.FND_PRINTER_DRIVERS;

SELECT * FROM apps.FND_PRINTER_INFORMATION where 1=1
--and PRINTER_STYLE like 'XXCNC%'
;

SELECT PRINTER,
       PRINT_STYLE,
       (SELECT PRINTER_TYPE
          FROM apps.FND_PRINTER
         WHERE PRINTER_NAME = PRINTER)
          PRINTER_TYPE
  FROM apps.FND_CONCURRENT_REQUESTS
 WHERE REQUEST_ID = :1

SQL-remove new line character from tables field

update TABLE_NAME
set COLUMN= replace(replace(COLUMN, CHR(13), ''), CHR(10), '')

SQL- ship confirm rule

 SELECT distinct wsh.name rule_name
 --, wdd.source_header_type_id , ott.attribute1,
 ,wda.delivery_id
              FROM wsh_ship_confirm_rules_v wsh,
                   fnd_lookup_values fnd,
                   wsh_delivery_assignments wda,
                   wsh_delivery_details wdd
                 , oe_transaction_types ott
             WHERE     wsh.name = fnd.meaning
                   AND fnd.lookup_type = 'KF DEF SHIP CONFIRM RULE'
                   AND wdd.delivery_detail_id = wda.delivery_detail_id
                   AND wda.delivery_id  in (  684472,674497)
                   AND wdd.source_code = 'OE'
                   AND wdd.source_header_type_id = ott.transaction_type_id
                AND ott.attribute1 = fnd.lookup_code
                   ;

SQL-Customer phone number

Query-Customer phone number

           SELECT raw_phone_number
--                 INTO x_shipto_contact
                 FROM hz_contact_points hcp,
                      hz_party_sites hps,
                      hz_cust_acct_sites_all hcas,
                      hz_cust_site_uses_all hcsu
                WHERE     hcp.owner_table_id = hps.party_site_id
                      AND hps.party_site_id = hcas.party_site_id
                      AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
                      AND hcsu.site_use_code = 'SHIP_TO'
                      AND hcas.cust_acct_site_id =
                             cust_accounts_rec.cust_acct_site_id        --6587
                      AND hcp.STATUS = 'A'
                      AND hcp.owner_table_name = 'HZ_PARTY_SITES'
                      AND hcp.contact_point_type = 'PHONE'
                      AND phone_line_type = 'GEN';
--if top one is blank

SELECT hcp.raw_phone_number
   --                    INTO x_shipto_contact
                       FROM hz_contact_points hcp,
                            hz_parties hp,
                            hz_cust_accounts_all hca
                      WHERE     hca.party_id = hcp.owner_table_id
                            AND hp.party_id = hca.party_id
                            AND hca.party_id = hcp.owner_table_id
                            AND hcp.owner_table_name = 'HZ_PARTIES'
                            AND hcp.contact_point_type = 'PHONE'
                            AND phone_line_type = 'GEN'
                            AND hcp.STATUS = 'A'
                            AND hca.cust_account_id =
                                   cust_accounts_rec.oracle_customer_id;

SQL- Query LOB

select  dbms_lob.substr( notes_detail, 4000, 1 ) from JTF_NOTES_TL JNT where jnt.jtf_note_id = 1260139;

SQL-DATES

Query -date and time

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of examples:



Description

Date Expression



Now

SYSDATE



Tomorow/ next day

SYSDATE + 1



Seven days from now

SYSDATE + 7



One hour from now

SYSDATE + 1/24



Three hours from now

SYSDATE + 3/24



An half hour from now

SYSDATE + 1/48



10 minutes from now

SYSDATE + 10/1440

30 seconds from now

SYSDATE + 30/86400

Tomorrow at 12 midnight

TRUNC(SYSDATE + 1)

Tomorrow at 8 AM

TRUNC(SYSDATE + 1) + 8/24

Next Monday at 12:00 noon

NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24

First day of the month at 12 midnight

TRUNC(LAST_DAY(SYSDATE ) + 1)

The next Monday, Wednesday or Friday at 9 a.m

TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

SQL-WSH - update OP delivery from oracle

query - WSH - update OP delivery

/*  this is the script to identify open deliveries but all lines are shipped
see tar SR 3-7848981781
*/

select distinct wdd.source_header_number, wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wdd.released_status = 'C'
and wnd.status_code not in ('IT', 'CL')
order by 1;


3) If you want to just close the reported delivery 459098, then you can run the following:
UPDATE wsh_new_deliveries
SET status_code = 'CL',
last_update_date = SYSDATE,
last_updated_by = -1
WHERE delivery_id = 459098
and status_code = 'OP';


/*  this is the script to set delivery status to closed
see tar SR 3-7848981781
*/

UPDATE wsh_new_deliveries
SET    status_code      = 'CL',
       last_update_date = SYSDATE,
       last_updated_by   = -1
WHERE delivery_id IN ( select distinct wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id  = wnd.delivery_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status = 'C'
and wnd.status_code = 'OP');

wsh_picking_batches