Different Types of discrete jobs

There are 2 types of discrete Jobs
1) Standard - Standard discrete jobs are used to manufacture a brand new product using Bill of Materials and Routings. E.g. There is a demand in the sales order for an Assembly item which in turn creates a WIP Job. For this case standard discrete job will be created.
2) Non-Standard - Non Standard discrete jobs are mostly used to repair a defective item. E.g. A product is returned by the customer for some defective reason. For this item a non-standard job is created and item is refurbished.

For BI Publisher Reports, cannot view Barcodes when viewing PDF output

the steps to view the barcode fonts when the report is previewed in PDF format from .rtf file.

The problem here is that the fonts are correctly installed in computer but when output is previewed instead of barcodes actual text is seen. One of the possible reason for this could be that the xdo.cfg file is not configured correctly. Below are the steps

1) Copy Barcode font in C:\Windows\Fonts directory.
2) Copy xdo.cfg in C:\Program Files\Oracle\XML Publisher Desktop\Template Builder for Word\config directory. The sample file is already available when BI Publisher desktop is installed.
3) Right click the font file and go to properties. Copy the .ttf file name. Double click the font file to get the family name.
4) Open the xdo.cfg file and paste as follows (My filename is FRE3OF9X.ttf and font family is Free 3 of 9 Extended)

[font family="Free 3 of 9 Extended" style="normal" weight="normal"]
[truetype path="C:\Windows\fonts\FRE3OF9X.ttf" /]
[/font]
Note: replace brackets([ & ]) with angle brackets as was unable to paste due to HTML restriction in comments. There should be sample already available in the xdo.cfg file.

5) Try your report should work fine and barcodes should be correctly displayed.

Notify when sales order is created

Based on a request from one of our reader, below are the steps on how to notify users when an order is booked or new line is inserted on a booked order.

The Alert below will send notification once a day in the morning at 8:00 am.

1) Define Alert

Query used is

SELECT ooh.order_number
    , ool.line_number||'.'||ool.shipment_number line_number
    , ordered_item, ordered_quantity, ool.flow_Status_code
INTO &order_num, &line_num,&Item_num, &Quantity, &line_Status
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
AND 
( ooh.booked_date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
 OR (ool.creation_Date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
   AND ool.creation_date > ooh.booked_date)
)

2) Define Actions
Click on the actions button and then actions Detail button and define message as shown in screenshot. Note that the message type is summary.

3) Define Action Sets
Click on action sets and then action set details and in the members tab enter the action defined in step 2

4) Schedule the Request
Navigate to Request --> Check and submit the alert. Based on the definition of alert it will be scheduled to run.

View Bardcode while running XML Publisher

Following steps should be completed in order to register your Barcode font to be viewed when report is run from Application.

1) From XML Publisher responsibility, create a font file in XML administration.
Navigation: XML Publisher Administrator -- Administration -- Font FilesCreate Font File
2) Create a font mapping set. Navigation: XML Publisher Administrator -- Administration -- Font Mapping
3) In the template screen , edit configuration -- FO Processing -- Font Mapping Set , provided the corresponding font mapping set
4) The last important step is to always use Territory column while defining Template. E.g. United States.

Now when report is generated from application, barcode font should be viewed.

Loading Multiple files using sqlldr in UNIX

Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting with sample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code

LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)

Shell Script Code

cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done

3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

Script to Convert Excel tab to CSV

cd /applmgr/custom/inbound/data
for i in *.xls
do
echo $i
newfile=$i.csv
awk 'BEGIN {
        FS = "\t"
        OFS = ","
     }
     {
        $1 = $1
        for (i = 1; i <= NF; i++) {
           if ($i == "") {
              $i = "null"
           }
        }
        print $0
     }' $i > $newfile
done

Query to findout the parameters and value sets associate to a ccp

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag",

       fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND fcpl.user_concurrent_program_name  =  'XX AR Conversion Program'  --

 ORDER BY fdfcuv.column_seq_num;

Query to find out the short name of an application

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name",

       fa.basepath                 "Basepath"

  FROM fnd_application     fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   -- AND fat.application_name = 'Payables'  --

 ORDER BY fat.application_name;

Query to display the status of ccp

SELECT

       fu.user_name                           "User ID",

       frt.responsibility_name                "Responsibility Used",

       fcr.request_id                         "Request ID",

       fcpt.user_concurrent_program_name      "Concurrent Program Name",

       flv2.meaning                           "Phase",

       flv1.meaning                           "Status",

       fcr.request_date                       "Request Date",

       fcr.requested_start_date               "Request Start Date",

       fcr.parent_request_id                  "Parent Request ID"

  FROM

       fnd_user                    fu,

       fnd_responsibility_tl       frt,

       fnd_concurrent_requests     fcr,

       fnd_concurrent_programs_tl  fcpt,

       fnd_lookup_values           flv1,

       fnd_lookup_values           flv2

 WHERE

       1=1

   --

   AND flv1.lookup_code           =  fcr.status_code

   AND flv1.lookup_type           =  'CP_STATUS_CODE'

   AND flv1.enabled_flag          =  'Y'

   AND flv1.view_application_id   <> 0

   --

   AND flv2.lookup_code           =  fcr.phase_code

   AND flv2.lookup_type           =  'CP_PHASE_CODE'

   AND flv2.enabled_flag          =  'Y'

   AND flv2.view_application_id   <> 0

   --

   AND fu.user_id                 =  fcr.requested_by

   AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id

   AND fcr.responsibility_id      =  frt.responsibility_id

   AND frt.LANGUAGE               =  USERENV ('LANG')

   AND fcpt.LANGUAGE              =  USERENV ('LANG')

   -- AND fcr.request_id = 7137350  --

   AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'  --

 ORDER BY fcr.request_date DESC;

Script to update the password of an user from back end

DECLARE

   v_user_name     VARCHAR2(30) :=  UPPER ('&USER_NAME');  -- change it

   v_new_password  VARCHAR2(30) :=  '&NEW_PASSWORD';       -- change it

  

   v_exists        PLS_INTEGER;

   v_status        BOOLEAN;

   e_user          EXCEPTION;

   e_pswd          EXCEPTION;

  

BEGIN

  

   -- Check if user exists

   BEGIN

      SELECT 1

        INTO v_exists

        FROM fnd_user u

       WHERE 1=1

         AND u.user_name = v_user_name;

        

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         RAISE e_user;

   END;

  

  

   -- Validate password

   IF (

            -- if password is less than 8 characters

            (LENGTH (v_new_password) < 8)

        OR

            -- if password does not contain any number

            (NOT REGEXP_LIKE (v_new_password, '[[:digit:]]'))

      )

   THEN

      RAISE e_pswd;

   END IF;

  

  

   -- Use API to change password

   v_status := fnd_user_pkg.ChangePassword

                     (

                        username     =>  v_user_name,

                        newpassword  =>  v_new_password

                     );

  

  

   IF v_status = TRUE THEN

      DBMS_OUTPUT.PUT_LINE ('The password has been successfully reset for ' ||

                            v_user_name);

      COMMIT;

   ELSE

      DBMS_OUTPUT.PUT_LINE ('Unable to reset password due to ' ||

                            SUBSTR (SQLERRM, 1, 100));

      ROLLBACK;

   END IF;

EXCEPTION

   WHEN e_user THEN

      DBMS_OUTPUT.PUT_LINE ('User ' || v_user_name || ' could not be found');

   WHEN e_pswd THEN

      DBMS_OUTPUT.PUT_LINE ('The password provided could not be validated');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('SQLERRM: ' || SQLERRM);

END;

Query to display DB links exists in the database

 SELECT obj.object_type         "Object Type",
       obj.owner               "Object Owner",
       obj.object_name         "Object Name",
       obj.status              "Object Status",
       dbl.db_link             "DB Link",
       dbl.username            "DB Link",
       dbl.host                "DB Host"
  FROM dba_objects  obj,
       dba_db_links dbl
 WHERE obj.object_name = dbl.db_link
   AND obj.object_type = 'DATABASE LINK';

Query to find the first and last day of month

SELECT TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') "First Day of Previous Month",

       TRUNC (SYSDATE, 'MM') - 1              "Last Day of Previous Month",

       TRUNC (SYSDATE, 'MM')                  "First Day of Current Month",

       TRUNC (LAST_DAY (SYSDATE))             "Last Day of Current Month"

  FROM DUAL;

Query to list Customer (Party), Account, Site data

 SELECT
       ----------------------------------------------------------
       -- Party Information
       ----------------------------------------------------------
       hp.party_number                      "Registry ID",
       hp.party_name                        "Party Name",
       hp.party_type                        "Party Type",
       DECODE(hp.status,
              'A', 'Active',
              'I', 'Inactive',
              hp.status)                    "Party Status",
       ----------------------------------------------------------
       -- Account Information
       ----------------------------------------------------------
       hca.account_number                   "Account Number",
       DECODE(hca.status,
              'A', 'Active',
              'I', 'Inactive',
              hca.status)                   "Account Status",
       hca.account_name                     "Account Description",
       hca.customer_class_code              "Classification",
       DECODE(hca.customer_type,
              'R', 'External',
              'I', 'Internal',
              hca.customer_type)            "Account Type",
       ----------------------------------------------------------
       -- Site Information
       ----------------------------------------------------------
       hps.party_site_number                "Customer Site Number",
       DECODE(hcas.status,
              'A', 'Active',
              'Inactive')                   "Site Status",
       DECODE(hcas.bill_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.bill_to_flag)            "Bill To Flag",
       DECODE(hcas.ship_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.ship_to_flag)            "Ship To Flag",
       hcas.cust_acct_site_id               "Customer Acct Site ID",
       ----------------------------------------------------------
       -- Address Information
       ----------------------------------------------------------
       hl.address1                          "Address1",
       hl.address2                          "Address2",
       hl.address3                          "Address3",
       hl.address4                          "Address4",
       hl.city                              "City",
       hl.state                             "State",
       hl.postal_code                       "Zip Code",
       ter.name                             "Territory",
       ----------------------------------------------------------
       -- DFF Information (specific to client)
       ----------------------------------------------------------
       hcas.attribute4                      "SMG Key",
       hcas.attribute8                      "GLN Key",
       hca.attribute3                       "Credit Approval Date",
       hca.attribute7                       "Credit Approved By",
       hca.attribute4                       "Acct Opened Date",
       hca.attribute5                       "Credit Collection Status",
       hca.attribute1                       "BPCS Last Trx Date",
       hca.attribute2                       "BPCS Avg Pay Days",
       hca.attribute6                       "BPCS RCM Reference",
       ----------------------------------------------------------
       -- Collector Information
       ----------------------------------------------------------
       col.name                             "Collector Name",
       ----------------------------------------------------------
       -- Account Profile Information
       ----------------------------------------------------------
       hcp.credit_checking                  "Credit Check Flag",
       hcp.credit_hold                      "Credit Hold Flag",
       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",
       hcpa.overall_credit_limit            "Credit Limit",
       hcpa.trx_credit_limit                "Order Credit Limit",
       ----------------------------------------------------------
       -- Attachment Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM fnd_documents_vl doc,
                   fnd_lobs         blo,
                   fnd_attached_documents att
             WHERE doc.media_id = blo.file_id
               AND doc.document_id = att.document_id
               AND att.entity_name = 'AR_CUSTOMERS'
               AND att.pk1_value   = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Attachment Flag",
       ----------------------------------------------------------
       -- Party Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND hzcar.relationship_type = 'ALL'
               AND ROWNUM = 1), 'N'
       ) "Party Relationship Flag",
       ----------------------------------------------------------
       -- Account Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Account Relationship Flag",
       ----------------------------------------------------------
       -- Party Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_parties hp2
             WHERE 1=1
               AND hp2.party_id = hp.party_id
               AND (
                    hp2.url IS NOT NULL OR
                    -- LENGTH(TRIM(hp.email_address)) > 5
                    INSTR(hp2.email_address, '@') > 0 OR
                    hp2.primary_phone_purpose IS NOT NULL
                    )
           ), 'N'
       ) "Party Contact Flag",
       ----------------------------------------------------------
       -- Account Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (SELECT hcar.party_id
                      FROM hz_cust_account_roles   hcar,
                           ar_contacts_v           acv
                     WHERE hcar.cust_account_id   = hca.cust_account_id
                       AND hcar.cust_account_role_id = acv.contact_id
                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only
                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
                    )
               AND ROWNUM = 1), 'N'
       ) "Account Contact Flag",
       ----------------------------------------------------------
       -- Site Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (
                      SELECT hcar.party_id
                        FROM hz_cust_account_roles   hcar,
                             ar_contacts_v           acv
                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id
                         AND hcar.cust_account_role_id  =  acv.contact_id
                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)
                   )
               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition
       ) "Site Contact Flag"
  FROM
       hz_parties              hp,
       hz_party_sites          hps,
       hz_cust_accounts_all    hca,
       hz_cust_acct_sites_all  hcas,
       hz_customer_profiles    hcp,
       hz_cust_profile_amts    hcpa,
       hz_locations            hl,
       ra_territories          ter,
       ar_collectors           col
 WHERE
       1=1
   AND hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id 
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcas.territory_id      =  ter.territory_id(+)
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
   ----
   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
   AND hp.status              = 'A'               -- only Active Parties/Customers
   ----
   -- following conditions are for testing purpose only
   -- comment/uncomment as needed
   ----
   -- AND hp.party_number        = 11530
   -- AND hca.account_number     = 32253 --32396 --31753 --32253 --31038
 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;