how to set context sensitive dff

make Component Information from global to context sensitive


- unfreeze dff definition
- in refenrece field : $PROFILES$.ORG_ID
 - choose (either displayed or Synch or Required)
-context value fields :

org1
code : org_id say 121 , Name : HOOLAHOOP , description : HOOLAHOOP
within that you can set what attribute column would you like to be enabled for that org.

another org :
code : org_id say 144 , Name : JUMPROPE , description : JUMPROPE
within that you can set what attribute column would you like to be enabled for that org.


write a script to update the attribute category :

update BOM_COMPONENTS_B b
set attribute_category='83'
 WHERE 1 = 1
AND attribute1 IS NOT NULL
and exists ( select 1
  FROM mtl_system_items_B iasy,
       bom_bill_of_materials bom,
       mtl_system_items_B icmp,
       mtl_parameters mp
 WHERE     1 = 1
       AND iasy.organization_id = mp.organization_id
       and mp.organization_id  = bom.organization_id
       AND iasy.inventory_item_id = bom.assembly_item_id
       AND iasy.organization_id = bom.organization_id
       AND bom.bill_sequence_id = b.bill_sequence_id
       AND b.component_item_id = icmp.inventory_item_id
       and icmp.organization_id = bom.organization_id
       )

;
--check for assembly n components.
SELECT SUBSTR (iasy.segment1, 1, 20) "Assembly",
       SUBSTR (icmp.segment1, 1, 20) "Component",
       SUBSTR (comp.component_quantity, 1, 8) "Quantity",
       SUBSTR (comp.effectivity_date, 1, 8) "From",
       SUBSTR (comp.disable_date, 1, 8) "To Date",
       comp.attribute_category,
       comp.attribute1,
       --bom.organization_id,    
--       bom.ALTERNATE_BOM_DESIGNATOR,
--       comp.OPERATION_SEQ_NUM,
       mp.organization_code
--       ,comp.component_item_id
--       , comp.bill_sequence_id
  FROM mtl_system_items_B iasy,
       bom_bill_of_materials bom,
       bom_components_b comp,
       mtl_system_items_B icmp,
       mtl_parameters mp
 WHERE     1 = 1
       AND iasy.organization_id = mp.organization_id
       and mp.organization_id  = bom.organization_id
       AND iasy.inventory_item_id = bom.assembly_item_id
       AND iasy.organization_id = bom.organization_id
       AND bom.bill_sequence_id = comp.bill_sequence_id
       AND comp.component_item_id = icmp.inventory_item_id
       and icmp.organization_id = bom.organization_id
       AND comp.attribute1 IS NOT NULL
       order by 1,2
   
   

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)