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

SQL - user errors from database

select *
from user_errors
where name like 'XXCNC_PURC_VENDOR_SITE_UPD_PKG%'

SQL- UOM MAX conversion Rate

SELECT * --UOM_CODE maxuom        
           FROM MTL_UOM_CONVERSIONS M1
          WHERE     conversion_rate =
                       (SELECT MAX (CONVERSION_RATE)
                          FROM MTL_UOM_CONVERSIONS M2
                         WHERE M2.INVENTORY_ITEM_ID = M1.INVENTORY_ITEM_ID)
                --AND M1.INVENTORY_ITEM_ID = 39548;

   SELECT primary_uom_code
           FROM mtl_system_items_b msi,
                mtl_parameters mpa
          WHERE inventory_item_id     = p_inventory_item_id
            AND msi.organization_id   = mpa.organization_id
            AND mpa.organization_code = 'MAS';


api - qty conversion
inv_convert.inv_um_conversion(:cp_rpt_uom,:cp_prim_uom,:cp_item_id,x_rate);

SQL- search a db object for a string

select * from ALL_SOURCE where text like '%some string%';

SQL- Multiple rows from Dual

Query - Multiple rows from Dual
select 'x',level from dual a  connect by level < 10

SQL -PO with Rcts, PO req and Internal Req

Query - PO


--PO with receipts
  SELECT
rsh.receipt_num
--
            ph.segment1 po_num,
--         ood.organization_name,
--         pol.po_line_id,
--         pll.quantity,
--         rsh.receipt_source_code,
--         rsh.vendor_id,
--         rsh.vendor_site_id,
--         rsh.organization_id,
--         rsh.shipment_num,
--         rsh.receipt_num,
--         rsh.ship_to_location_id,
--         rsh.bill_of_lading,
--         rsl.shipment_line_id,
--         rsl.QUANTITY_SHIPPED,
--         rsl.QUANTITY_RECEIVED
--         rct.transaction_type,
--         rct.transaction_id,
--         NVL (rct.source_doc_quantity, 0) transaction_qty
    FROM
         rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pol,
         po_line_locations_all pll,
         po_headers_all ph,
         org_organization_definitions ood
   WHERE     1 = 1
        -- AND TO_CHAR (rct.creation_date, 'YYYY') IN ('2010', '2011')
         AND rct.po_header_id = ph.po_header_id
         AND rct.po_line_location_id = pll.line_location_id
         AND rct.po_line_id = pol.po_line_id
         AND rct.shipment_line_id = rsl.shipment_line_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsh.ship_to_org_id = ood.organization_id
         and ph.segment1 in ('300046929')




--Purchase Requisition Details
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prh.type_lookup_code,
       prl.line_num,
       prl.line_type_id,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'PURCHASE'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id
       --AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')


-- INternal requisitions
SELECT prh.segment1 "Req #",
       prh.creation_date,
       prh.created_by,
       poh.segment1 "PO #",
       ppx.full_name "Requestor Name",
       prh.description "Req Description",
       prh.authorization_status,
       prh.note_to_authorizer,
       prl.line_num,
       prl.line_type_id,
       prl.source_type_code,
       prl.item_description,
       prl.unit_meas_lookup_code,
       prl.unit_price,
       prl.quantity,
       prl.quantity_delivered,
       prl.need_by_date,
       prl.note_to_agent,
       prl.currency_code,
       prl.rate_type,
       prl.rate_date,
       prl.quantity_cancelled,
       prl.cancel_date,
       prl.cancel_reason
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       per_people_x ppx,
       po_headers_all poh,
       po_distributions_all pda
 WHERE     prh.requisition_header_id = prl.requisition_header_id
       AND ppx.person_id = prh.preparer_id
       AND prh.type_lookup_code = 'INTERNAL'
       AND prd.requisition_line_id = prl.requisition_line_id
       AND pda.req_distribution_id(+) = prd.distribution_id
       AND pda.po_header_id = poh.po_header_id(+)
       AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

SQL -shipped orders

EXEC apps.mo_global.set_policy_context('S','83');

  select ooh.order_number , ool.ordered_item , ool.shipping_quantity_uom , ool.shipped_quantity  , ooh.customer_number, ooh.booked_date , ool.ship_from , wdd.source_header_id
    from   oe_order_lines_v ool
    , oe_order_headers_v ooh
    ,wsh_delivery_details wdd
  where ool.header_id = ooh.header_id
  and   ool.header_id              =  wdd.source_header_id
  AND    ool.line_id                =  wdd.source_line_id
  AND    ool.inventory_item_id      =  wdd.inventory_item_id
  AND    wdd.released_status        =  'C'
   and ool.shipping_quantity_uom = 'EA'
   and ooh.customer_number in ('1000', '1020')
  AND    trunc(ool.actual_shipment_date) >   trunc(sysdate)-2 ;
  and    trunc(ooh.ordered_date) >   trunc(sysdate)-2 ;

Profile to change the view of concurrent request output

Profile -change output
Viewer: Text
Value = APPLETVIEWER

SQL- Array from string of values

CURSOR c_email (l_list IN VARCHAR2)
      IS
             SELECT REGEXP_SUBSTR (l_list,
                                   '[^,]+',
                                   1,
                                   LEVEL)
                       email
               FROM DUAL
         CONNECT BY REGEXP_SUBSTR (l_list,
                                   '[^,]+',
                                   1,
                                   LEVEL)
                       IS NOT NULL;

FORM COMPILE Command

FORM COMPILE
compile fmb from custom top and now it should.

export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US

form compile
frmcmp_batch module=$AU_TOP/forms/US/XXCNCOMTMPT.fmb output_file=$XXCNC_TOP/forms/US/XXCNCOMTMPT.fmx userid=apps/xxxx module_type=form compile_all=special

frmcmp_batch module=$AU_TOP/forms/US/XXCNC_INV_TAG_COUNT.fmb output_file=$XXCNC_TOP/forms/US/XXCNC_INV_TAG_COUNT.fmx userid=apps/aaaaaa module_type=form compile_all=special

frmcmp_batch module=$XXCNC_TOP/forms/US/XXCNC_OM_KCN_RMA_V1.fmb output_file=$XXCNC_TOP/forms/US/XXCNC_OM_KCN_RMA_V1.fmx userid=apps/zzzzzz module_type=form compile_all=special


FORMS_PATH
echo $FORMS_PATH


cd $XXCNC_TOP/forms/US
cp formfilename.fmb $AU_TOP/forms/US/
cd $AU_TOP/forms/US/
frmcmp_batch module=$AU_TOP/forms/US/formfilename.fmb output_file=$XXCNC_TOP/forms/US/formfilename.fmx userid=apps/zzzzz module_type=form compile_all=special

SQL-order lines exist where invoice is generated but inventory not reduced

Following script can be used to know if such order lines exist where invoice is generated but inventory not reduced:

SELECT
  (SELECT h.order_number
  FROM oe_order_headers_all h
  WHERE h.header_id = l.header_id
  ) Order_Num,
  Oe_Order_Misc_Pub.Get_Concat_Line_Number(l.line_id) Line_Num,
  l.line_id,
  l.org_id operating_unit,
  l.creation_date
FROM oe_order_lines_all l
WHERE l.open_flag = 'N'
AND l.flow_status_code = 'CLOSED'
AND l.invoice_interface_status_code = 'YES'
AND l.ordered_quantity > 0
AND l.item_type_code IN ('STANDARD', 'INCLUDED')
AND NOT EXISTS
  (SELECT 'Inventory Interface Record in MTI, to be processed later.'
  FROM mtl_transactions_interface mti
  WHERE mti.trx_source_line_id = l.line_id
  AND mti.transaction_type_id = 33
  AND mti.source_code = 'ORDER ENTRY'
  )
AND NOT EXISTS
  (SELECT 'Record of a completed material transaction for SO Issue.'
  FROM mtl_material_transactions mmt
  WHERE mmt.trx_source_line_id = l.line_id
  AND mmt.transaction_type_id = 33
  AND mmt.source_code = 'ORDER ENTRY'
  )
AND NOT EXISTS
  (SELECT 'Delivery details because this is a ship-only line.'
  FROM wsh_delivery_details wdd
  WHERE wdd.source_code = 'OE'
  AND wdd.source_line_id = l.line_id
  )
ORDER BY Order_Num ASC;

Apex tutorial

http://web.nmsu.edu/~jkreie/oracle/DatabaseTopics.html

sql-db locks

SELECT DECODE(request,0,'Holder: ',' Waiter: ')||a.sid sess,
decode(request,0,to_char(a.inst_id),' '||to_char(a.inst_id)) inst,b.audsid ,
id1, id2, lmode, request, a.type, a.sid,
b.sql_hash_value hash,module,round(last_call_et/60,0) min ,
b.audsid
FROM gV$LOCK a, gv$session b
WHERE (id1, id2, a.type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
and a.sid = b.sid
and a.inst_id = b.inst_id
ORDER BY id1, request;

SQL-set context in toad

EXEC apps.mo_global.set_policy_context('S','83');
EXEC apps.mo_global.set_policy_context('S','141');


      -- to run from toad
      mo_global.init ('ONT');
      fnd_global.apps_initialize (user_id        => g_user_id,
                                  resp_id        => g_resp_id,
                                  resp_appl_id   => g_resp_appl_id);
      mo_global.set_policy_context ('S', 141);


    exec FND_GLOBAL.APPS_INITIALIZE(1130, 50850, 200);


  MO_GLOBAL.get_current_org_id,
                         FND_GLOBAL.CONC_REQUEST_ID,
                         SYSDATE,
                         FND_GLOBAL.USER_ID,
                         FND_GLOBAL.USER_ID,

select fnd_profile.value('ORG_ID') from dual;

FND_GLOBAL.APPS_INITIALIZE(fnd_global.user_id, fnd_global.resp_id, fnd_global.resp_appl_id);

select * from fnd_responsibility_vl where responsibility_name like 'CTY%' ;

select fnd_global.org_name from dual;