Script to Set the Global Info (FND_GLOBAL.APPS_INITIALIZE)

PROCEDURE set_global_info(
   p_responsibility_id      IN             NUMBER := fnd_api.g_miss_num
 , p_user_id                IN             NUMBER := fnd_api.g_miss_num
 , p_resp_appl_id           IN             NUMBER := 275
 , p_msg_count              OUT            NUMBER
 , p_msg_data               OUT            VARCHAR2
 , p_return_status          OUT            VARCHAR2
)
IS
   l_api_name               CONSTANT VARCHAR2(30) := 'Set_Global_Info';
   l_value_conversion_error BOOLEAN := FALSE;
   l_return_status          VARCHAR2(1);
   l_dummy                  VARCHAR2(1);
   l_temp_num               NUMBER;

   CURSOR l_resp_csr
   IS
      SELECT 'x'
        FROM fnd_responsibility
       WHERE responsibility_id = p_responsibility_id
         AND application_id = p_resp_appl_id;

   CURSOR l_user_csr
   IS
      SELECT 'x'
        FROM fnd_user
       WHERE user_id = p_user_id;

   l_resp_csr_rec                                    l_resp_csr%ROWTYPE;
BEGIN
   IF    p_responsibility_id IS NULL
      OR p_responsibility_id = fnd_api.g_miss_num
   THEN
      IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
      THEN
         -- Define the Custom message as XX_RESP_ID_REQD with
         -- Description as "A valid responsibility is required."
         fnd_message.set_name('XX_APPL_NAME', 'XX_RESP_ID_REQD');
         fnd_msg_pub.ADD;
      END IF;

      RAISE fnd_api.g_exc_error;
   END IF;

   OPEN l_resp_csr;

   FETCH l_resp_csr
    INTO l_dummy;

   IF l_resp_csr%NOTFOUND
   THEN
      IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
      THEN
         -- Define the Custom message as XX_RESP_ID_INVALID with
         -- Description as "The specified responsibility is invalid."
         fnd_message.set_name('XX_APPL_NAME', 'XX_RESP_ID_INVALID');
         fnd_msg_pub.ADD;
      END IF;

      CLOSE l_resp_csr;

      RAISE fnd_api.g_exc_error;
   ELSE
      CLOSE l_resp_csr;
   END IF;

   IF    p_user_id IS NULL
      OR p_user_id = fnd_api.g_miss_num
   THEN
      IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
      THEN
         -- Define the Custom message as XX_USER_ID_REQD with
         -- Description as "A valid user ID is required."
         fnd_message.set_name('XX_APPL_NAME', 'XX_USER_ID_REQD');
         fnd_msg_pub.ADD;
      END IF;

      RAISE fnd_api.g_exc_error;
   END IF;

   OPEN l_user_csr;

   FETCH l_user_csr
    INTO l_dummy;

   IF l_user_csr%NOTFOUND
   THEN
      IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
      THEN
         -- Define the Custom message as XX_USER_ID_REQD with
         -- Description as "The specified user ID is invalid."
         fnd_message.set_name('XX_APPL_NAME', 'XX_USER_ID_INVALID');
         fnd_msg_pub.ADD;
      END IF;

      CLOSE l_user_csr;

      RAISE fnd_api.g_exc_error;
   ELSE
      CLOSE l_user_csr;
   END IF;

   fnd_global.apps_initialize(user_id  => p_user_id
                            , resp_id  => p_responsibility_id
                            , resp_appl_id => p_resp_appl_id
                             );
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      p_return_status                       := fnd_api.g_ret_sts_error;
      fnd_msg_pub.count_and_get(p_count     => p_msg_count, 
                                p_data      => p_msg_data);
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      p_return_status                := fnd_api.g_ret_sts_unexp_error;
      fnd_msg_pub.count_and_get(p_count => p_msg_count, 
                                p_data  => p_msg_data);
   WHEN OTHERS
   THEN
      p_return_status    := fnd_api.g_ret_sts_unexp_error;

      IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
      THEN
         fnd_msg_pub.add_exc_msg(p_pkg_name       => g_pkg_name,                                          p_procedure_name => l_api_name);
      END IF;

      fnd_msg_pub.count_and_get(p_count => p_msg_count, 
                                p_data  => p_msg_data);
END set_global_info;

Useful Scripts for Unix

CAT

The cat command reads one or more files and prints them to standard output. The operator > can be used to combine multiple files into one. The operator>> can be used to append to an existing file.

SYNTAX
The syntax for the cat command is:
cat [options] [files]

OPTIONS

Option	Description
-e	$ is printed at the end of each line. This option must be used with -v.
-s	Suppress messages pertaining to files that do not exist.
-t	Each tab will display as ^I and each form feed will display as ^L. This option must be used with -v.
-u	Output is printed as unbuffered.
-v	Display control characters and nonprinting characters

EXAMPLE
cat file1
cat file1 file2 > all
cat file1 >> file2
MAIL

The mail command allows you to read or send mail. If users is left blank, it allows you to read mail. If usershas a value, then it allows you send mail to those users.
SYNTAX
The syntax for the mail command is:
mail [options] [users]

OPTIONS FOR READING MAIL

Option	Description
-e	Check if mail exists. Exit status is 0 if mail exists and 1 if mail does not exist.
-f file	Read mail from mailbox called file.
-F names	Forward mail to names.
-h	Displays messages in a window.
-i	Ignore interrupts
-p	Displays all messages.
-P	Displays all messages with header lines.
-q	Terminate when an interrupt occurs.
-r	Displays oldest messages first.
-U	Convert uucp-type addresses to Internet format.
-v	Verbose.

OPTIONS FOR SENDING MAIL

Option	Description
-m type	Display a "Message-type:" line at the heading of the message, followed by type.
-t	Display a "To:" line at the heading of the message with the names of the recipients.
-w	Send mail to users without waiting for a remote transfer program to finish.
-F	Save message in a file called the name of the first recipient.
-h n	If message has not been sent after n network connections, do not send message.
-i	Ignore interrupts
-r address	address is the return address for mail messages.
-s subject	Displays subject in the subject header.
-U	Convert uucp-type addresses to Internet format.
-v	Verbose.

MAN

The man command displays the online manual pages.

SYNTAX
The syntax for the man command is:
man [options] [ [section] subjects]

OPTIONS

Option	Description
-	Output is piped through the more command.
-d	Debug.
-F	Search the MANPATH directories.
-f files	Display a summary (one line) for each file.
-k keywords	Displays the header lines that contain any of the keywords.
-M path	Searchs in the path instead of the MANPATH directories.
-t	Format the pages with troff.
-T mac	Display using mac.

EXAMPLE
man -f tech


TAIL
The tail command displays the last ten lines of the file.

SYNTAX
The syntax for the tail command is:
tail [options] [file]

OPTIONS

Option	Description
-f	Follow the file as it grows.
-r	Displays the lines in the reverse order.
-n[k]	Displays the file at the nth item from the end of the file.
+n[k]	Displays the file at the nth item from the beginning of the file.

EXAMPLE
tail -r tech

FTP

The ftp command allows you to transfer files to and from a remote server.

SYNTAX
The syntax for the ftp command is:
ftp [options] [hostname]

OPTIONS

Option	Description
-d	Debugging is enabled.
-g	Filename globbing is disabled.
-i	Interactive prompting is disabled.
-n	When you are initially connecting, auto-login is disabled.
-v	Display all responses from the server.

EXAMPLE
ftp tech
ftp -v tech

MORE
The more command displays the file called namein the screen. The RETURN key displays the next line of the file. The spacebar displays the next screen of the file.

SYNTAX
The syntax for the more command is:
more [options] [files]

OPTIONS

Option	Description
-c	Page through the file by clearing the window. (not scrolling).
-d	Displays "Press space to continue, 'q' to quit"
-f	Count logical lines rather than screen lines (wrapping text)
-l	Ignores form feed (^L) characters.
-r	Display all control characters.
-s	Displays multiple blank lines as one blank line.
-u	Does not display underline characters and backspace (^H).
-w	Waits for a user to press a key before exiting.
-n	Displays n lines per window.
+num	Displays the file starting at line number num.
+/pattern	Displays the file starting at two lines before the pattern.

EXAMPLE
more -d tech


GREP

The grep command allows you to search one file or multiple files for lines that contain a pattern. Exit status is 0 if matches were found, 1 if no matches were found, and 2 if errors occurred.

SYNTAX
The syntax for the grep command is:
grep [options] pattern [files]

OPTIONS

Option	Description
-b	Display the block number at the beginning of each line.
-c	Display the number of matched lines.
-h	Display the matched lines, but do not display the filenames.
-i	Ignore case sensitivity.
-l	Display the filenames, but do not display the matched lines.
-n	Display the matched lines and their line numbers.
-s	Silent mode.
-v	Display all lines that do NOT match.
-w	Match whole word.

EXAMPLE
grep -c tech file1
PS
The ps command displays active processes.

SYNTAX
The syntax for the ps command is:
ps [options]

OPTIONS

Option	Description
-a	Displays all processes on a terminal, with the exception of group leaders.
-c	Displays scheduler data.
-d	Displays all processes with the exception of session leaders.
-e	Displays all processes.
-f	Displays a full listing.
-glist	Displays data for the list of group leader IDs.
-j	Displays the process group ID and session ID.
-l	Displays a long listing
-plist	Displays data for the list of process IDs.
-slist	Displays data for the list of session leader IDs.
-tlist	Displays data for the list of terminals.
-ulist	Displays data for the list of usernames.

EXAMPLE
ps -ef
ps -aux


Hopefully this has helped some of you out and I’d appreciate any comments you have.

API Script to add the responsibilities to a user

DECLARE

   CURSOR c_resp

   IS

      SELECT fav.application_short_name

           , fav.application_name

           , frv.responsibility_key

           , frv.responsibility_name

        FROM fnd_application_vl fav

           , fnd_responsibility_vl frv

       WHERE frv.application_id = fav.application_id

         AND frv.responsibility_name IN ( 'Application Developer');

 l_user_name             VARCHAR2 (15) := '&USER_NAME';

 l_appl_short_name       fnd_application_vl.application_short_name%TYPE;

 l_resp_name             fnd_responsibility_vl.responsibility_name%TYPE;

 l_resp_key              fnd_responsibility_vl.responsibility_key%TYPE;

 l_description           VARCHAR2 (100) := 'Adding Responsibility to the user using script';

BEGIN

   FOR resp_rec IN c_resp

   LOOP

      l_appl_short_name    := resp_rec.application_short_name;

      l_resp_key           := resp_rec.responsibility_key;

      l_resp_name          := resp_rec.responsibility_name;

      BEGIN

         fnd_user_pkg.addresp (username          => l_user_name

                             , resp_app          => l_appl_short_name

                             , resp_key          => l_resp_key

                             , security_group    => 'STANDARD'

                             , description       => l_description

                             , start_date        => SYSDATE

                             , end_date          => NULL

                              );

         COMMIT;

         DBMS_OUTPUT.put_line ('The responsibility ' || l_resp_name || ' is added to the user ' || l_user_name);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (   'Responsibility '

                                  || l_resp_name

                                  || ' IS NOT added to the user '

                                  || l_user_name

                                  || ' due to '

                                  || SQLCODE

                                  || '; '

                                  || SUBSTR (SQLERRM, 1, 250)

                                 );

            DBMS_OUTPUT.put_line ('');

            ROLLBACK;

      END;

   END LOOP;

END;

API script to Disable the enabled Responsibilities for a User

DECLARE

   --cursor to get all inactive users

   CURSOR cur_inactive_user

   IS

      SELECT fu.user_id

           , fd.responsibility_id

           , fd.responsibility_application_id

           , fd.security_group_id

           , fd.start_date

           , fd.end_date

        FROM fnd_user fu

           , fnd_user_resp_groups_direct fd

       WHERE fu.user_id = fd.user_id

         AND (   fu.end_date <= SYSDATE

              OR fu.end_date IS NOT NULL)

         AND fd.end_date IS NOT NULL

         AND fu.user_id = :p_user_id;

BEGIN

   FOR rec_inactive_user IN cur_inactive_user

   LOOP

--checking if the responsibility is assigned to the user

 IF (fnd_user_resp_groups_api.assignment_exists (rec_inactive_user.user_id, rec_inactive_user.responsibility_id, rec_inactive_user.responsibility_application_id, rec_inactive_user.security_group_id))
     THEN

         -- Call API to End date the responsibility

         fnd_user_resp_groups_api.update_assignment

        (user_id           => rec_inactive_user.user_id

       , responsibility_id => rec_inactive_user.responsibility_id

       , responsibility_application_id => rec_inactive_user.responsibility_application_id

       , security_group_id => rec_inactive_user.security_group_id

       , start_date        => rec_inactive_user.start_date

       , end_date          => NULL

       , description       => NULL);

         COMMIT;

      END IF;

   END LOOP;

END;

API script to Enable the Disabled Responsibilities for a User

DECLARE
   --cursor to get all inactive users
   CURSOR cur_inactive_user
   IS
      SELECT fu.user_id
           , fd.responsibility_id
           , fd.responsibility_application_id
           , fd.security_group_id
           , fd.start_date
           , fd.end_date
        FROM fnd_user fu
           , fnd_user_resp_groups_direct fd
       WHERE fu.user_id = fd.user_id
         AND fu.user_id = :p_user_id;
BEGIN
   FOR rec_inactive_user IN cur_inactive_user
   LOOP
      fnd_user_resp_groups_api.update_assignment(
        user_id           => rec_inactive_user.user_id
      , responsibility_id => rec_inactive_user.responsibility_id
      , responsibility_application_id =>                rec_inactive_user.responsibility_application_id
    , security_group_id          => rec_inactive_user.security_group_id
    , start_date                 => rec_inactive_user.start_date
    , end_date                   => NULL
    , description                => NULL );
      COMMIT;
   END LOOP;
END;

R12: How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information -- 816374.1

A. Contract Header Data

select * from OKC_K_HEADERS_ALL_B 
where contract_number like :p_contract_number;

B. Contract Line Data

Note: in OKC_K_LINES_B the chr_id field is only populated with the contract header id for contract lines. For contract sublines, this value is NULL.  Dnz_chr_id is populated with the contract header id for both lines and sublines. 

B1. This SQL takes data from views rather than from actual contracts tables and is useful for reviewing data but not ideal for verifying if base tables hold correct data.

  SELECT DISTINCT oal.line_number
                , oll.lse_name
                , oal.sts_code "Status"
                , oal.trn_code
                , oal.lse_id
                , old.service_name
                , oal.currency_code "Currency|Code"
                , TO_CHAR( oal.start_date
                         , 'DD-MON-YYYY' )
                     "Start Date"
                , TO_CHAR( oal.end_date
                         , 'DD-MON-YYYY' )
                     "End Date"
                , qpl.name "Price List Name"
                , cust_acct_id
                , bill_to_site_use_id
                , inv_rule_id
                , ship_to_site_use_id
                , ship_to_site_use_id
                , acct_rule_id
                , usage_period
                , usage_type
                , uom_quantified
                , billing_schedule_type
                , invoice_text
    FROM oks_auth_lines_v oal
       , okc_launch_lgrid_v oll
       , qp_pricelists_lov_v qpl
       , oks_line_details_v old
   WHERE oal.id = oll.id
     AND cle_id IS NULL
     AND qpl.price_list_id = oal.price_list_id
     AND old.contract_id = oll.chr_id
     AND oll.chr_id = '<value of id taken from query A>'

ORDER BY TO_NUMBER( line_number );


B2. Data taken directly from contract table. (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed).

SELECT *
  FROM okc_k_lines_b
 WHERE chr_id IN (SELECT id
                    FROM okc_k_headers_all_b

                   WHERE contract_number = '<contract number>');

C. Contract Subline Data

Note: When you add a subline to a contract OKC_K_LINES_B is populated with data, some of the data created there for each subline is internal data. Use the LSE_ID to restrict the data returned when querying. 

C1. Query for all the sublines on a contract with a Level type that can be seen when authoring the contract (i.e. restricts to lines which have Level of Product, Site, Item, System, Customer or Site). Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed.

SELECT id
     , line_number
     , cle_id
     , sts_code
     , hidden_ind
     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"
     , object_version_number
     , price_negotiated
     , price_level_ind
     , price_unit
     , price_unit_percent
     , price_type
     , currency_code
     , price_list_id
     , price_list_line_id
     , item_to_price_yn
     , pricing_date
     , date_terminated
     , start_date
     , end_date
  FROM okc_k_lines_b
 WHERE dnz_chr_id IN (SELECT id
                        FROM okc_k_headers_all_b
                       WHERE contract_number = '<contract number>')

   AND lse_id IN (8, 7, 9, 10, 11, 35);


C2. Query for contract sublines for a given contract line only. Replace <parent line number> with the line number of the required contract line (e.g. 1, 2. 3), taken either from the contract form, or from query B2. (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed).

SELECT id
     , line_number
     , cle_id
     , sts_code
     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"
     , object_version_number
     , price_negotiated
     , price_level_ind
     , price_unit
     , price_unit_percent
     , price_type
     , currency_code
     , price_list_id
     , price_list_line_id
     , item_to_price_yn
     , pricing_date
     , date_terminated
     , start_date
     , end_date
  FROM okc_k_lines_b
 WHERE dnz_chr_id IN (SELECT id
                        FROM okc_k_headers_all_b
                       WHERE contract_number = '<contract number>')
   AND cle_id IN (SELECT id
                    FROM okc_k_lines_b
                   WHERE chr_id IN (SELECT id
                                      FROM okc_k_headers_all_b
                                     WHERE contract_number = '<contract number>')
                     AND line_number = '<parent line number>')
   AND lse_id IN (8, 7, 9, 10, 11, 35);

C3. This query returns the inventory item for a given contract subline where the Level = Product (i.e. the subline is for a particular install base instance).

SELECT kl.line_number
     , ks.name
     , i.segment1
  FROM okc_k_headers_all_b kh
     , okc_k_lines_b kl
     , okc_k_items ki
     , okc_line_styles_v ks
     , csi_item_instances c
     , mtl_system_items_b i
 WHERE kh.contract_number = '<contract number>'
   AND kh.contract_number_modifier IS NULL                                                                 --can be populated
   AND kh.id = kl.dnz_chr_id
   AND kh.id = ki.dnz_chr_id
   AND kl.id = ki.cle_id
   AND kl.lse_id = ks.id
   AND ki.jtot_object1_code IN ('OKX_CUSTPROD')
   AND c.last_vld_organization_id = i.organization_id
   AND TO_NUMBER( ki.object1_id1 ) = c.instance_id
   AND c.inventory_item_id = i.inventory_item_id;

D. Contract Billing Data

D1. This query shows the billing invoice details. Note that -99 will be shown for invoice number if the 'Service Contracts Fetch Receivables Info For Billing' concurrent program has not been run after Autoinvoice has been run.

SELECT DISTINCT d.contract_number
              , a.trx_number "Invoice Number"
              , TO_CHAR( b.date_billed_from
                       , 'DD-MON-YYYY HH24-MI' )
                   "Bill From"
              , TO_CHAR( b.date_billed_to
                       , 'DD-MON-YYYY HH24-MI' )
                   "Bill To"
              , b.amount
  FROM oks_bill_transactions a
     , oks_bill_txn_lines aa
     , oks_bill_cont_lines b
     , okc_k_lines_b c
     , okc_k_headers_all_b d
 WHERE a.id = aa.btn_id
   AND aa.bcl_id = b.id
   AND b.cle_id = c.cle_id
   AND c.dnz_chr_id = d.id
   AND d.id = '<contract id from query A>';

D2. This query shows the billing transaction details. The data in this table is shown in the History tab of the Billing Schedule form in the contract. 

For the bill_action, the codes have the following meanings: 
Regular Invoice -RI, 
Termination Credit - TR, 
Averaging - AV, 
Settlement Invoice - SRI, 
Settlement Credit - STR.

  SELECT hdr.contract_number "Contract"
       , hdr.contract_number_modifier "Modifier"
       , hdr.id
       , TO_CHAR( cont.creation_date
                , 'DD-MON-YYYY HH24:MI' )
            "Creation Date"
       , bill_action
       , btn_id "Billing Transaction ID"
       , amount
       , TO_CHAR( date_billed_from
                , 'DD-MON-YYYY' )
            "Date Billed From"
       , TO_CHAR( date_billed_to
                , 'DD-MON-YYYY' )
            "Date Billed To"
    FROM oks_bill_cont_lines cont
       , okc_k_lines_b line
       , okc_k_headers_all_b hdr
   WHERE hdr.id = line.dnz_chr_id
     AND cont.cle_id = line.id
     AND hdr.id = '<contract id from query A>'

ORDER BY cont.creation_date;

D3. This query returns data about the contract Line ids corresponding to each invoice as well as invoice details.

SELECT bcl.id
     , bcl.cle_id
     , bcl.btn_id
     , bcl.bill_action
     , okl.id "Line id"
     , okh.id "Contract id"
     , btn.trx_number "Invoice"
     , bcl.date_billed_from
     , bcl.date_billed_to
  FROM oks_bill_cont_lines bcl
     , okc_k_lines_b okl
     , okc_k_headers_all_b okh
     , oks_bill_transactions btn
     , oks_bill_txn_lines btl
 WHERE okh.contract_number = '<contract number>'
   AND okh.id = okl.dnz_chr_id
   AND okl.cle_id IS NULL
   AND okl.id = bcl.cle_id
   AND btn.id = bcl.btn_id
   AND btl.btn_id = btn.id

   AND btl.bill_instance_number IS NOT NULL;

D4. This query returns information about what the contract billing schedule for a contract and can be used to investigate amounts expected to be billed in a billing period.

SELECT TO_CHAR( bcl.id )
     , TO_CHAR( bsl.id )
     , TO_CHAR( lvl.id )
     , lvl.date_start
     , bsl.date_billed_from
     , lvl.date_end
     , bsl.date_billed_to
     , bcl.date_next_invoice
     , lvl.date_transaction
     , lvl.date_to_interface
     , lvl.date_completed
     , TO_CHAR( rul_id )
     , TO_CHAR( lvl.parent_cle_id )
     , bsl.amount
  FROM oks_bill_sub_lines bsl
     , oks_bill_cont_lines bcl
     , oks_level_elements lvl
     , okc_k_lines_b kl
     , okc_k_headers_all_b kh
 WHERE kh.contract_number = '<contract number>'
   AND kl.dnz_chr_id = kh.id
   AND lvl.dnz_chr_id = kh.id
   AND bcl.cle_id = kl.id
   AND bcl.id = bsl.bcl_id
   AND lvl.cle_id = bsl.cle_id;

E. Receivables Interface Data

E1. Query to return all the data in the RA interface table for a given service contract. This will return the data populated into the table by Service Contracts Main Billing. Note that this query will not return any data if Autoinvoice has been run since the records are deleted from this table once they have been successfully processed by Autoinvoice.

SELECT *
  FROM ra_interface_lines_all
 WHERE sales_order = '<contract number>';

F. Subscription Contracts

F1. How to find the install base instance created for the subscription line item. (Note that when you enter a subscription line, the application automatically creates an Oracle Install Base item instance. This is what this query is retrieving).
Note: the last line in the query can be commented out if your contract has no modifier.

SELECT osh.instance_id
     , okh.contract_number
     , okh.contract_number_modifier
     , okl.line_number
  FROM oks_subscr_header_b osh
     , okc_k_headers_all_b okh
     , okc_k_lines_b okl
 WHERE osh.dnz_chr_id = okh.id
   AND osh.cle_id = okl.id
   AND okl.chr_id = okh.id
   AND okh.contract_number = '<contract number>'
   AND NVL( okh.contract_number_modifier, '-' ) = NVL( '<contract_modifier>', '-' );

F2. Query to find the install base instances created by a Subscription Contract as a result of subscription fulfillment.

SELECT csi.instance_number
  FROM oks_subscr_elements ose
     , csi_item_instances csi
 WHERE ose.dnz_chr_id IN (SELECT id
                            FROM okc_k_headers_all_b
                           WHERE contract_number = '<contract number>'
                             AND NVL( contract_number_modifier, '-' ) = NVL( '<contract modifier>', '-' ))
   AND ose.order_line_id = csi.last_oe_order_line_id;

f3. QUERY TO find which subscription contract line created THE install base INSTANCE, FOR A s/ubscription fulfillment.

SELECT okh.contract_number
     , okh.contract_number_modifier
     , okl.line_number
  FROM oks_subscr_elements ose
     , csi_item_instances csi
     , okc_k_headers_all_b okh
     , okc_k_lines_b okl
 WHERE csi.instance_number = '<Instance Number>'
   AND ose.order_line_id = csi.last_oe_order_line_id
   AND okh.id = ose.dnz_chr_id
   AND okl.chr_id = okh.id
   AND okl.id = ose.dnz_cle_id;