How to select user and responsibility details associated to an Application(FND) user

-- Query to derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name

2. Responsibility Name
 

  SELECT u.user_name,

              u.start_date user_start_date,

              u.end_date user_end_date,

              u.email_address user_email_address,

              r.responsibility_name,

              g.start_date resp_assign_start_date,

              g.end_date resp_assign_end_date,

              g.user_id,

              r.responsibility_id

    FROM apps.fnd_user u,

             apps.fnd_user_resp_groups g,

             apps.fnd_responsibility_tl r

   WHERE     1 = 1

        AND r.responsibility_id = g.responsibility_id

        AND g.user_id = u.user_id

        AND u.user_name = NVL(:p_user_name, u.user_name)

        AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)

ORDER BY u.user_name, r.responsibility_name DESC;

How to fetch Aging Bucket line details for a delinquent Customer account

Following Select statement will fetch Aging bucket line details for a particular Customer to send dunning letter:-

Parameters:

•   Dunning Plan Name
•   Customer Account Number

SELECT l.aging_bucket_line_id,
                l.type Aging_Bucket_Line_Type,
                l.days_start,
                l.days_to
  FROM ar_aging_bucket_lines l,
              ar_aging_buckets b,
              iex_dunning_plans_vl d
 WHERE 1 = 1
   AND d.name = :p_dunning_plan_name   
   AND d.aging_bucket_id = b.aging_bucket_id
   AND b.aging_bucket_id = l.aging_bucket_id
   AND (SELECT MAX (TRUNC (SYSDATE) - TRUNC (ar.due_date)) days
                 FROM iex_delinquencies del,
                             ar_payment_schedules ar,
                             hz_cust_accounts hca
             WHERE 1 = 1
             AND hca.cust_account_id = del.cust_account_id
             AND hca.account_number = :p_account_number
            AND del.payment_schedule_id = ar.payment_schedule_id
            AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
            BETWEEN l.days_start AND l.days_to
            AND EXISTS                    (SELECT 1
                     FROM iex_ag_dn_xref x
                     WHERE 1=1
                      AND d.dunning_plan_id = x.dunning_plan_id
                      AND d.aging_bucket_id = x.aging_bucket_id
                      AND x.aging_bucket_line_id = l.aging_bucket_line_id);

How to delete a Concurrent Program and Concurrent Executable

Concurrent program cannot be deleted from Oracle Applications front-end, it can only be disabled.

Try to delete a concurrent program from Oracle Applications front-end:-

 

 

 

 

 

 

 

                                                                                             

 

Execute the following database script using fnd_program.delete_program to delete Concurrent program programmatically:-
 

 

  BEGIN
       fnd_program.delete_program (program_short_name => 'XX_CP', -- Conc. Executable Short Name
                                                  application => 'AR' -- Program Application Short Name
                                                           );
    COMMIT;
END;

 

After the program deletion script is executed, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Program: XX Concurrent Program, Ctrl + F11

Note: - The concurrent program is deleted

 

The Concurrent executable can be deleted from the Oracle Applications front-end after the associated Concurrent Program is deleted or it can be deleted by using a database script:-

Press on “Delete” icon and save to delete Concurrent executable:-

 

 

 

 

 

 

                                                                                                                                                                      

Following database script using fnd_program.delete_executable can be used to delete Concurrent executable programmatically:-

BEGIN
  fnd_program.delete_executable (executable_short_name => 'XX_EXEC', -- Conc. Executable Short Name
                                                 application => 'AR' -- Program Application Short Name
                                                    );
   COMMIT;
END;

 

 

After the executable deletion script is executed in the database, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Executable: XX_EXEC, Ctrl + F11

Note: - The concurrent executable is deleted

SQL Query to display Current Statement of Active Session/Program

Following SQL query will fetch "Current Statement" for the active session passed as parameter:-

 Parameters:-

     p_inst_id -- Instance Id
     p_sid      -- Session Id
 
     SELECT s.sid,
       s.client_info,
       s.machine,
       s.program,
       s.type,
       s.logon_time,
       s.osuser,
       sq.sorts,
       sq.disk_reads,
       sq.buffer_gets,
       sq.rows_processed,
       sq.sqltype,
       sq.sql_text
  FROM gv$session s,
               gv$sql sq
 WHERE s.sql_hash_value = sq.hash_value
   AND s.inst_id = :p_inst_id           -- replace with ID from above
   AND s.sid = :p_sid                   -- replace with instID from above
   AND sq.inst_id = s.inst_id;

 

 -- Select statement which provides information on sessions and queries which cause maximum disk reads and waits

 

  SELECT SUBSTR (ss.username, 1, 8) username,
         ss.osuser "USER",
         ar.module || ' @ ' || ss.machine client,
         ss.process pid,
         TO_CHAR (ar.last_load_time, 'DD-Mon HH24:MM:SS') load_time,
         ar.disk_reads disk_reads,
         ar.buffer_gets buffer_gets,
         SUBSTR (ss.lockwait, 1, 10) lockwait,
         w.event event,
         ss.status,
         ar.sql_fulltext sql
    FROM v$session_wait w,
         v$sqlarea ar,
         v$session ss,
         v$timer t
   WHERE     ss.sql_address = ar.address
         AND ss.sql_hash_value = ar.hash_value
         AND ss.sid = w.sid(+)
         AND ss.status = 'ACTIVE'
         AND w.event != 'client message'
ORDER BY ss.lockwait ASC, ss.username, ar.disk_reads DESC;

Select statement with Bill-To & Ship-To address for AR Open Transactions

-- Select Statement which details the AR Open Transactions' Bill-To, Ship-To and Paying Site details along with accounting combinations

SELECT sob.name sob_name,
         hou.name ou_name,
         t.trx_number,
         hp.party_name,
         acct.account_number,
         rctt.name Transaction_type,
         t.bill_to_site_use_id,
            hl.address1
         || ','
         || hl.address2
         || ','
         || hl.city
         || ','
         || hl.county
         || ','
         || NVL (hl.state, hl.province)
            bill_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            ship_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            paying_cust_address,
         l.customer_trx_line_id,
         t.customer_trx_id,
         l.line_number,
         l.line_type,
         l.reason_code,
         l.quantity_invoiced,
         l.inventory_item_id,
         l.description,
         l.uom_code,
         l.unit_selling_price,
         (d.amount) extended_amount,
         p.amount_due_remaining balance_due,
         gcc.segment1,
         gcc.segment2,
         gcc.segment3,
         gcc.segment4,
         gcc.segment5,
         gcc.segment6,
         gcc.segment7,
         gcc.segment8,
         d.account_class,
         d.gl_date,
         d.acctd_amount,
         sp.salesrep_number,
         s.revenue_percent_split,
         l.link_to_cust_trx_line_id
FROM ra_customer_trx_all t,
         ar_payment_schedules_all p,
         gl_sets_of_books sob,
         hr_operating_units hou,
         ra_customer_trx_lines_all l,
         ra_cust_trx_line_gl_dist_all d,
         gl_code_combinations gcc,
         ra_cust_trx_line_salesreps_all s,
         jtf_rs_salesreps sp,
         ra_cust_trx_types_all rctt,
         ra_terms rt,
         hz_parties hp,
         hz_cust_site_uses_all hcsu,
         hz_cust_acct_sites_all hcas,
         hz_cust_accounts acct,
         hz_party_sites hps,
         hz_locations hl,
         hz_cust_site_uses_all hcsu1,
         hz_cust_acct_sites_all hcas1,
         hz_party_sites hps1,
         hz_locations hl1,
         hz_cust_accounts acct_pay,
         hz_cust_site_uses_all hcsu2,
         hz_cust_acct_sites_all hcas2,
         hz_party_sites hps2,
         hz_locations hl2
   WHERE     t.customer_trx_id = p.customer_trx_id
         AND p.status = 'OP'
         AND rctt.org_id = t.org_id
         AND t.set_of_books_id = sob.set_of_books_id
         AND t.org_id = hou.organization_id
         AND t.customer_trx_id = l.customer_trx_id
         AND t.cust_trx_type_id = rctt.cust_trx_type_id
         AND d.customer_trx_id = l.customer_trx_id
         AND d.customer_trx_line_id = l.customer_trx_line_id
         AND d.code_combination_id = gcc.code_combination_id
         AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
         AND s.customer_trx_id(+) = t.customer_trx_id
         AND s.salesrep_id = sp.salesrep_id(+)
         AND s.org_id = sp.org_id(+)
         AND t.term_id = rt.term_id(+)
         AND acct.party_id = hp.party_id
         AND t.bill_to_customer_id = acct.cust_account_id
         AND t.bill_to_site_use_id = hcsu.site_use_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND acct.cust_account_id = hcas.cust_account_id
         AND hcsu.site_use_code = 'BILL_TO'
         AND hcas.party_site_id = hps.party_site_id
         AND hps.location_id = hl.location_id
         AND t.ship_to_site_use_id = hcsu1.site_use_id(+)
         AND hcas1.cust_acct_site_id(+) = hcsu1.cust_acct_site_id
         AND p.class IN ('INV', 'DM')
         AND hcsu1.site_use_code(+) = 'SHIP_TO'
         AND hcas1.party_site_id = hps1.party_site_id(+)
         AND hps1.location_id = hl1.location_id(+)
         AND t.paying_customer_id = acct_pay.cust_account_id(+)
         AND t.paying_site_use_id = hcsu2.site_use_id(+)
         AND hcas2.cust_acct_site_id(+) = hcsu2.cust_acct_site_id
         AND hcas2.party_site_id = hps2.party_site_id(+)
         AND NVL (hcsu2.site_use_code, 'BILL_TO') = 'BILL_TO'
         AND hps2.location_id = hl2.location_id(+)
         AND EXISTS
                (SELECT 1
                   FROM ra_customer_trx_all t,
                        ar_payment_schedules_all p1,
                        gl_sets_of_books sob,
                        hr_operating_units hou,
                        hz_cust_accounts acct_bill_to,
                        hz_cust_accounts acct_pay,
                        jtf_rs_salesreps sp,
                        ra_terms rt,
                        ra_cust_trx_types_all rctt
                  WHERE     t.customer_trx_id = p1.customer_trx_id
                        AND p1.status = 'OP'
                        AND p1.customer_trx_id = p.customer_trx_id
                        AND t.set_of_books_id = sob.set_of_books_id
                        AND t.org_id = hou.organization_id
                        AND t.bill_to_customer_id =
                               acct_bill_to.cust_account_id
                        AND t.paying_customer_id = acct_pay.cust_account_id(+)
                        AND t.primary_salesrep_id = sp.salesrep_id(+)
                        AND t.org_id = sp.org_id(+)
                        AND t.term_id = rt.term_id(+)
                        AND t.cust_trx_type_id = rctt.cust_trx_type_id
                        AND t.org_id = rctt.org_id)
ORDER BY trx_number, customer_trx_line_id;

How to create and apply receipt programmatically

---------------------------------------------------

--- AR_RECEIPT_API_PUB.Create_and_apply

--- By Oracle-ebspro

--- Sample code to Create and Apply Receipts

---------------------------------------------------

DECLARE

   p_api_version                    NUMBER;

   p_init_msg_list                  VARCHAR2 (240);

   p_commit                         VARCHAR2 (240);

   p_validation_level               NUMBER;

   p_usr_currency_code              VARCHAR2 (240);

   p_usr_exchange_rate_type         VARCHAR2 (240);

   p_exchange_rate_type             VARCHAR2 (240);

   p_exchange_rate                  NUMBER;

   p_exchange_rate_date             DATE;

   p_factor_discount_amount         NUMBER;

   p_receipt_date                   DATE;

   p_postmark_date                  DATE;

   p_customer_number                VARCHAR2 (240);

   p_customer_bank_account_id       NUMBER;

   p_customer_bank_account_num      VARCHAR2 (240);

   p_customer_bank_account_name     VARCHAR2 (240);

   p_location                       VARCHAR2 (240);

   p_customer_receipt_reference     VARCHAR2 (240);

   p_remittance_bank_account_num    VARCHAR2 (240);

   p_remittance_bank_account_name   VARCHAR2 (240);

   p_receipt_method_name            VARCHAR2 (240);

   p_doc_sequence_value             NUMBER;

   app_ussgl_transaction_code       VARCHAR2 (240);

   p_anticipated_clearing_date      DATE;

   p_called_from                    VARCHAR2 (240);

   p_comments                       VARCHAR2 (240);

   p_issuer_name                    VARCHAR2 (240);

   p_issue_date                     DATE;

   p_issuer_bank_branch_id          NUMBER;

   p_amount                         NUMBER;

   p_receipt_number                 VARCHAR2 (240);

   p_receipt_method_id              NUMBER;

   p_customer_name                  VARCHAR2 (240);

   p_customer_id                    NUMBER;

   p_currency_code                  VARCHAR2 (10);

   p_gl_date                        DATE;

   p_deposit_date                   DATE;

   p_customer_site_use_id           NUMBER;

   p_override_remit_account_flag    VARCHAR2 (1);

   p_remittance_bank_account_id     NUMBER;

   p_maturity_date                  DATE;

   x_return_status                  VARCHAR2 (1);

   x_msg_count                      NUMBER;

   x_msg_data                       VARCHAR2 (240);

   p_cr_id                          NUMBER;

   p_global_attribute_rec           AR_RECEIPT_API_PUB.global_attribute_rec_type;

   p_attribute_rec                  AR_RECEIPT_API_PUB.attribute_rec_type;

   p_call_payment_processor         VARCHAR2 (100);

   p_trx_number                     VARCHAR2 (100);

   p_receipt_comments               VARCHAR2 (1000);

   p_customer_trx_id                ra_customer_trx.customer_trx_id%TYPE;

   p_installment                    ar_payment_schedules.terms_sequence_number%TYPE;

   p_applied_payment_schedule_id    ar_payment_schedules.payment_schedule_id%TYPE;

   p_amount_applied                 ar_receivable_applications.amount_applied%TYPE;

   p_amount_applied_from            ar_receivable_applications.amount_applied_from%TYPE;

   p_trans_to_receipt_rate          ar_receivable_applications.trans_to_receipt_rate%TYPE;

   p_discount                       ar_receivable_applications.earned_discount_taken%TYPE;

   p_apply_date                     ar_receivable_applications.apply_date%TYPE;

   p_apply_gl_date                  ar_receivable_applications.gl_date%TYPE;

   p_customer_trx_line_id           ar_receivable_applications.applied_customer_trx_line_id%TYPE;

   p_line_number                    ra_customer_trx_lines.line_number%TYPE;

   p_show_closed_invoices           VARCHAR2 (240);

   p_move_deferred_tax              VARCHAR2 (240);

   p_link_to_trx_hist_id            ar_receivable_applications.link_to_trx_hist_id%TYPE;

   app_attribute_rec                AR_RECEIPT_API_PUB.attribute_rec_type;

   p_ussgl_transaction_code         ar_cash_receipts.ussgl_transaction_code%TYPE;

   app_comments                     ar_receivable_applications.comments%TYPE;

   app_global_attribute_rec         AR_RECEIPT_API_PUB.global_attribute_rec_type;

   l_user_id                        NUMBER := fnd_global.user_id;

   l_resp_id                        NUMBER := fnd_profile.VALUE ('RESP_ID');

   l_resp_appl_id                   NUMBER

                                       := fnd_profile.VALUE ('RESP_APPL_ID');

   l_receipt_num                    VARCHAR2 (30) := '&P_Receipt_Num';

   l_customer                       VARCHAR2 (240) := '&P_Customer';

   l_currency                       VARCHAR2 (10) := '&P_Currency';

   l_trx_num                        VARCHAR2 (30) := '&P_Trx_Num';

   l_receipt_method                 VARCHAR2 (30) := '&P_Receipt_Method';

   l_receipt_method_id              NUMBER;

  

BEGIN

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);

   SELECT receipt_method_id

     INTO l_receipt_method_id

     FROM ar_receipt_methods

    WHERE name = l_receipt_method;

   p_receipt_number := l_receipt_num;

   p_receipt_method_id := l_receipt_method_id;

   p_amount := 1000;

   p_customer_name := l_customer;

   p_currency_code := l_currency;

   p_receipt_date := SYSDATE;

   p_gl_date := SYSDATE;

   p_deposit_date := SYSDATE;

   p_trx_number := l_trx_num;

   p_amount_applied := 400;

   p_apply_date := SYSDATE;

   AR_RECEIPT_API_PUB.Create_and_apply (

      p_api_version                    => 1.0,

      p_init_msg_list                  => FND_API.G_TRUE,

      p_commit                         => FND_API.G_TRUE,

      p_validation_level               => FND_API.G_VALID_LEVEL_FULL,

      p_usr_currency_code              => p_usr_currency_code,

      p_currency_code                  => p_currency_code,

      p_usr_exchange_rate_type         => p_usr_exchange_rate_type,

      p_exchange_rate_type             => p_exchange_rate_type,

      p_exchange_rate                  => p_exchange_rate,

      p_exchange_rate_date             => p_exchange_rate_date,

      p_amount                         => p_amount,

      p_factor_discount_amount         => p_factor_discount_amount,

      p_receipt_number                 => p_receipt_number,

      p_receipt_date                   => p_receipt_date,

      p_gl_date                        => p_gl_date,

      p_maturity_date                  => p_maturity_date,

      p_postmark_date                  => p_postmark_date,

      p_customer_id                    => p_customer_id,

      p_customer_name                  => p_customer_name,

      p_customer_number                => p_customer_number,

      p_customer_bank_account_id       => p_customer_bank_account_id,

      p_customer_bank_account_num      => p_customer_bank_account_num,

      p_customer_bank_account_name     => p_customer_bank_account_name,

      p_location                       => p_location,

      p_customer_site_use_id           => p_customer_site_use_id,

      p_customer_receipt_reference     => p_customer_receipt_reference,

      p_override_remit_account_flag    => p_override_remit_account_flag,

      p_remittance_bank_account_id     => p_remittance_bank_account_id,

      p_remittance_bank_account_num    => p_remittance_bank_account_num,

      p_remittance_bank_account_name   => p_remittance_bank_account_name,

      p_deposit_date                   => p_deposit_date,

      p_receipt_method_id              => p_receipt_method_id,

      p_receipt_method_name            => p_receipt_method_name,

      p_doc_sequence_value             => p_doc_sequence_value,

      p_ussgl_transaction_code         => p_ussgl_transaction_code,

      p_anticipated_clearing_date      => p_anticipated_clearing_date,

      p_called_from                    => p_called_from,

      p_attribute_rec                  => p_attribute_rec,

      p_global_attribute_rec           => p_global_attribute_rec,

      p_receipt_comments               => p_receipt_comments,

      app_comments                     => app_comments,

      p_issuer_name                    => p_issuer_name,

      p_issue_date                     => p_issue_date,

      p_issuer_bank_branch_id          => p_issuer_bank_branch_id,

      p_customer_trx_id                => p_customer_trx_id,

      p_trx_number                     => p_trx_number,

      p_installment                    => p_installment,

      p_applied_payment_schedule_id    => p_applied_payment_schedule_id,

      p_amount_applied                 => p_amount_applied,

      p_amount_applied_from            => p_amount_applied_from,

      p_trans_to_receipt_rate          => p_trans_to_receipt_rate,

      p_discount                       => p_discount,

      p_apply_date                     => p_apply_date,

      p_apply_gl_date                  => p_apply_gl_date,

      app_ussgl_transaction_code       => app_ussgl_transaction_code,

      p_customer_trx_line_id           => p_customer_trx_line_id,

      p_line_number                    => p_line_number,

      p_show_closed_invoices           => p_show_closed_invoices,

      p_move_deferred_tax              => p_move_deferred_tax,

      p_link_to_trx_hist_id            => p_link_to_trx_hist_id,

      app_attribute_rec                => app_attribute_rec,

      x_return_status                  => x_return_status,

      x_msg_count                      => x_msg_count,

      x_msg_data                       => x_msg_data,

      p_cr_id                          => p_cr_id,

      p_call_payment_processor         => FND_API.G_FALSE,

      app_global_attribute_rec         => app_global_attribute_rec);

   IF (x_return_status = 'S')

   THEN

      COMMIT;

      DBMS_OUTPUT.put_line ('SUCCESS');

      DBMS_OUTPUT.put_line (

         'Return Status            = ' || SUBSTR (x_return_status, 1, 255));

      DBMS_OUTPUT.put_line ('Message Count             = ' || x_msg_count);

      DBMS_OUTPUT.put_line ('Message Data            = ' || x_msg_data);

      DBMS_OUTPUT.put_line ('p_cr_id                  = ' || p_cr_id);

   ELSE

      ROLLBACK;

      DBMS_OUTPUT.put_line (

         'Return Status    = ' || SUBSTR (x_return_status, 1, 255));

      DBMS_OUTPUT.put_line ('Message Count     = ' || TO_CHAR (x_msg_count));

      DBMS_OUTPUT.put_line (

         'Message Data    = ' || SUBSTR (x_msg_data, 1, 255));

      DBMS_OUTPUT.put_line (

         APPS.FND_MSG_PUB.Get (p_msg_index   => APPS.FND_MSG_PUB.G_LAST,

                               p_encoded     => APPS.FND_API.G_FALSE));

      IF x_msg_count >= 0

      THEN

         FOR I IN 1 .. 10

         LOOP

            DBMS_OUTPUT.put_line (

               I || '. '

               || SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),

                          1,

                          255));

         END LOOP;

      END IF;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);

END;