AR - Receipt API

-- --------------------------------------------------------------------
-- Script      : XYKAARDRNEW_SH.sql
-- Name        : AR NEW DEBTOR RECEIPTS BATCH
-- Author      : ABHISHEK VAITLA
-- Date        : JUNE 09,2011
-- Description :
--
--
-- Modification history :
--
--

/* $header: XYKAARDRNEW_SH.sql   1.1 2011/06/09  11:17:00  $ */
SET serveroutput on size 1000000;
--
--

DECLARE
   /* to get ar debtor receipts */
   CURSOR debtor_receipts
   IS
      SELECT   hdr.x_header_id header_id, x_date x_date, x_outlet_code outlet_code,
         x_division_code division_code,
            x_receipt_number
         || '-'
         || (SELECT short_code
               FROM hr_operating_units
              WHERE organization_id = lns.x_inv_org_id) receipt_number,
         x_cheque_number cheque_number, x_bank_name bank_name,
         x_receipt_source receipt_source, x_payment_type payment_type,
         lns.x_inv_customer_id customer_id,
         customer_memo_line customer_memo_line,
         lns.x_inv_cust_bill_to_id bill_to_site_use_id,
         x_set_of_books_id set_of_books_id, hdr.x_org_id org_id,
         hdr.x_gl_cc_id memo_cc_id, x_memo_line_id memo_line_id,
         SUM (lns.x_applied_amount) amount, x_comments comments,
         lns.x_inv_org_id
    FROM xyka_cash_debtor_recpt_hdr_v hdr, xyka_cash_debtor_recpt_lines_v lns
   WHERE hdr.x_header_id = lns.x_header_id
    -- AND hdr.x_org_id = lns.x_org_id
     AND NVL (x_ar_transferred_flag, 'N') = 'N'
     AND x_receipt_source = ('Customer')
     AND NVL (x_gl_transferred_flag, 'N') = 'Y'
     AND x_set_of_books_id = '&1'
     AND hdr.x_org_id = NVL ('&2', hdr.x_org_id)
  AND (x_date >= TO_DATE ('&3', 'RRRR/MM/DD HH24:MI:SS') OR '&3' IS NULL)
   AND (x_date <= TO_DATE ('&4', 'RRRR/MM/DD HH24:MI:SS') OR '&4' IS NULL)
/* AND x_date BETWEEN NVL (TO_DATE ('&3', 'RRRR/MM/DD HH24:MI:SS'),
                             TRUNC (TO_DATE (SYSDATE, 'DD-MON-RRRR'))
                            )
                    AND NVL (TO_DATE ('&4', 'RRRR/MM/DD HH24:MI:SS'),
                             TRUNC (TO_DATE (SYSDATE, 'DD-MON-RRRR'))
                            )
*/
GROUP BY hdr.x_header_id,
         x_date,
         x_outlet_code,
         x_division_code,
         x_receipt_number,
         x_inv_ou,
         x_cheque_number,
         x_bank_name,
         x_receipt_source,
         x_payment_type,
         x_inv_customer_id,
         customer_memo_line,
         x_inv_cust_bill_to_id,
         x_set_of_books_id,
         hdr.x_org_id,
         hdr.x_gl_cc_id,
         x_memo_line_id,
         x_comments,
         lns.x_inv_org_id;

   /* get receipt batch source based on receipt source */
   CURSOR batch_source (p_org_id NUMBER)
   IS
      SELECT ABS.batch_source_id, ABS.last_batch_num,
             ABS.default_receipt_class_id, ABS.default_receipt_method_id,
             ABS.org_id, arm.remit_bank_acct_use_id, aaa.bank_account_id
        FROM ar_batch_sources_all ABS,
             ar_receipt_method_accounts_all arm,
             ce_bank_acct_uses_all aaa
       WHERE ABS.default_receipt_method_id = arm.receipt_method_id
         AND arm.remit_bank_acct_use_id = aaa.bank_acct_use_id
         AND UPPER (NAME) LIKE '%DEBTOR%RECEIPT%'
         AND ABS.org_id = p_org_id;

   /* to get applied invoices against receipts from debtor receipt */
   CURSOR ar_invoices (p_header_id NUMBER, p_org_id NUMBER)
   IS
      SELECT x_line_id line_id, x_header_id header_id,
             x_customer_trx_number trx_number,
             x_customer_trx_id customer_trx_id, x_inv_customer_id,
             x_inv_cust_bill_to_id, x_payment_schedule_id payment_schedule_id,
             x_applied_amount applied_amount, x_due_date due_date,
             x_inv_org_id org_id
        FROM xyka_cash_debtor_inv_lines
       WHERE x_header_id = p_header_id AND x_inv_org_id = p_org_id;

   --  define variables

   --
   t_user_id               NUMBER                                := 0;
   t_org_id                NUMBER                                := 0;
   t_login_id              NUMBER                                := 0;
   t_req_id                NUMBER                                := 0;
   t_date                  DATE                                  := NULL;
   t_message               VARCHAR2 (32000)                      := NULL;
   tot_rec                 NUMBER                                := 0;
   tot_err_rec             NUMBER                                := 0;
   --

   --  define user specified parameters

   --
   lv_rec_count            NUMBER                                := 1;
   lv_org_id               NUMBER (15)                           := 0;
   lv_valid_cnt            NUMBER (15)                           := 0;
   lv_null_cnt             NUMBER (15)                           := 0;
   lv_chart_ok             BOOLEAN                               := FALSE;
   lv_curr                 VARCHAR2 (15)                         := NULL;
   lv_err_flag             VARCHAR2 (1)                          := 'N';
   l_batch_id              NUMBER                                := 0;
   l_cash_receipt_id       NUMBER                                := 0;
   l_batch_number          NUMBER;
   l_control_amt           NUMBER                                := 0;
   l_control_count         NUMBER                                := 0;
   l_set_of_books_id       NUMBER    := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
   l_org_id                NUMBER;
   l_site_use_id           NUMBER;
   l_receipt_line_id       NUMBER                                := 1;
   l_batch_source_id       NUMBER;
   l_receipt_class_id      NUMBER;
   l_receipt_method_id     NUMBER;
   l_bank_branch_id        NUMBER;
   l_bank_account_use_id   NUMBER;
   l_customer_id           NUMBER;
   l_invoice_count         NUMBER                                := 0;
   l_source_id             NUMBER;
   l_amt_applied_from      NUMBER;
   l_discount_taken        NUMBER;
   l_currency_code         VARCHAR2 (100);
   --
   l_return_status         VARCHAR2 (1);
   l_msg_count             NUMBER;
   l_msg_data              VARCHAR2 (1000);
   l_count                 NUMBER;
   l_msg_data_out          VARCHAR2 (1000);
   l_mesg                  VARCHAR2 (1000);
   l_err_msg               VARCHAR2 (1000);
   p_count                 NUMBER;
   --
   l_receipt_number        VARCHAR2 (40);
   l_dr_amount             NUMBER;
   l_cr_amount             NUMBER;
   l_app_attribute_rec     ar_receipt_api_pub.attribute_rec_type;
   l_payment_number        VARCHAR2 (20);
   l_cr_id                 NUMBER;
-- ---------------------------------------------------------------------
BEGIN
   -- Initializes the required variables
   --fnd_global.apps_initialize (1111, 50680, 222);
   DBMS_OUTPUT.put_line
         (   'Program XYKAARDR (AR Debtor Receipts batch) started   at .....'
          || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
         );

   BEGIN
      SELECT currency_code
        INTO l_currency_code
        FROM gl_ledgers
       WHERE ledger_id = l_set_of_books_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_currency_code := NULL;
   END;

   FOR j IN debtor_receipts
   LOOP
      FOR s IN batch_source (j.x_inv_org_id)
      LOOP
         l_batch_source_id := s.batch_source_id;
         l_receipt_class_id := s.default_receipt_class_id;
         l_receipt_method_id := s.default_receipt_method_id;
         l_bank_account_use_id := s.remit_bank_acct_use_id;
         l_batch_number := s.last_batch_num;
      END LOOP;

      mo_global.set_policy_context ('S', j.x_inv_org_id);
      ar_receipt_api_pub.create_cash
                       (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,
                        x_return_status                   => l_return_status,
                        x_msg_count                       => l_msg_count,
                        x_msg_data                        => l_msg_data,
                        p_currency_code                   => l_currency_code,
                        p_amount                          => j.amount,
                        p_receipt_number                  => j.receipt_number,
                        p_receipt_date                    => j.x_date,
                        p_gl_date                         => j.x_date,
                        p_customer_id                     => j.customer_id,
                        p_customer_site_use_id            => j.bill_to_site_use_id,
                        p_org_id                          => j.x_inv_org_id,
                        p_remittance_bank_account_id      => l_bank_account_use_id,
                        p_receipt_method_id               => l_receipt_method_id,
                        p_cr_id                           => l_cash_receipt_id,
                        p_comments                        => j.comments
                       );

      IF l_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
      ELSIF l_msg_count > 1
      THEN
         LOOP
            p_count := p_count + 1;
            l_msg_data :=
                        fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF l_msg_data IS NULL
            THEN
               EXIT;
            END IF;

            DBMS_OUTPUT.put_line ('Message' || p_count || '.' || l_msg_data);
            l_err_msg := l_err_msg || l_msg_data;
         END LOOP;
      END IF;

      /* validates the result */
      IF l_cash_receipt_id IS NULL
      THEN
         t_message :=
               t_message
            || ' Receipt creation failed. Error message : '
            || l_err_msg;
      ELSE
         FOR l IN ar_invoices (j.header_id, j.x_inv_org_id)
         LOOP
            IF l.trx_number <> 'On Account'
            THEN
               --l_amt_applied_from := NULL;
               --l_discount_taken := 0;

               /* calls the api to apply receipt against the invoice*/
               ar_receipt_api_pub.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_cash_receipt_id                  => l_cash_receipt_id,
                      p_customer_trx_id                  => l.customer_trx_id,
                      p_applied_payment_schedule_id      => l.payment_schedule_id,
                      --p_discount                      => l_discount_taken,
                      p_amount_applied                   => l.applied_amount,
                      --p_amount_applied_from           => l_amt_applied_from,
                      p_org_id                           => j.x_inv_org_id,
                      p_apply_date                       => j.x_date,
                      p_apply_gl_date                    => j.x_date,
                      x_return_status                    => l_return_status,
                      x_msg_count                        => l_msg_count,
                      x_msg_data                         => l_msg_data
                     );

               IF l_msg_count = 1
               THEN
                  DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
               ELSIF l_msg_count > 1
               THEN
                  LOOP
                     p_count := p_count + 1;
                     l_msg_data :=
                        fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

                     IF l_msg_data IS NULL
                     THEN
                        EXIT;
                     END IF;

                     t_message := p_count || '.' || l_msg_data;
                  END LOOP;
               END IF;
            END IF;

            UPDATE xyka_cash_debtor_inv_lines
               SET x_ar_flag = 'Y',ar_cash_receipt_id = l_cash_receipt_id
             WHERE x_header_id = j.header_id AND x_inv_org_id = j.x_inv_org_id;
         END LOOP;
      END IF;

      UPDATE xyka_cash_debtor_recpt_hdr
         SET -- ar_cash_receipt_id = l_cash_receipt_id,
             x_ar_transferred_flag = 'Y'
       WHERE x_header_id = j.header_id;

      tot_rec := tot_rec + 1;
   END LOOP;

   COMMIT;

   IF tot_rec = 0
   THEN
      DBMS_OUTPUT.put_line ('There is no record for Transfer.');
   ELSE
      DBMS_OUTPUT.put_line
         (   'Total records inserted for Program XYKAARDRNEW_SH (Open Financials Receipts batch)  ... :'
          || TO_CHAR (tot_rec)
         );
   END IF;

   DBMS_OUTPUT.put_line
                (   'Program XYKAARDRNEW_SH (Open Financials Receipts batch) '
                 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                );
   DBMS_OUTPUT.put_line (t_message);
END;
/

-- -----------------------  end of script -----------------

--
-- --------------------------------------------------------------------
-- script      : xykaardr_sh.sql
-- name        : ar debtor receipts batch
-- author      : anjireddy chanti
-- date        : august 13,2005
-- description :
--
--
-- modification history :
--
--

/* $header: xykaarri.sql  1.1 2005/07/28  11:11:00  $ *
set serveroutput on size 1000000;

--
--
declare
   --
   --  define cursor
   --


   /* to get the ar debtor receipt batch *

   cursor dr_batch is
       select distinct x_date,x_receipt_source
       from xyka_cash_debtor_recpt_hdr_v
       where x_ar_transferred_flag='n'
    and x_gl_transferred_flag <>'c'
       and x_receipt_source not in ('non - ar - yka','non - ar - property','non - ar - uet','non - ar - get','non - ar - aeo')
        and x_date between trunc(to_date(sysdate, 'dd-mon-rrrr')) and trunc(to_date(sysdate, 'dd-mon-rrrr'))
    ----and (x_date between to_date(sysdate,'rrrr/mm/dd hh24:mi:ss') and to_date(sysdate,'rrrr/mm/dd hh24:mi:ss'))
        and x_org_id = '&1';




  /* get receipt batch source based on receipt source */
      /* --(property payment method(1121) yka payment method(1120)) *

 cursor batch_source(p_batch_source_id number) is
   select abs.batch_source_id ,
             abs.last_batch_num,
      abs.default_receipt_class_id,
      abs.default_receipt_method_id,
      abs.org_id,
      arm.bank_account_id,
      aaa.bank_branch_id,
          aaa.set_of_books_id
   from ar_batch_sources_all abs,
     ar_receipt_method_accounts_all arm,
     ap_bank_accounts_all aaa 
  where abs.default_receipt_method_id=arm.receipt_method_id
  and arm.bank_account_id=aaa.bank_account_id
  and abs.batch_source_id=p_batch_source_id;




  /* to get ar debtor receipts *

    cursor debtor_receipts(p_receipt_date date,p_receipt_source varchar2) is
    select     x_header_id            header_id,
           x_date                x_date,
           x_outlet_code            outlet_code,
           x_division_code         division_code,
           x_receipt_number        receipt_number,
           x_cheque_number            cheque_number,
           x_bank_name              bank_name,
               x_receipt_source        receipt_source,
           x_payment_type            payment_type,
           x_source_id            source_id,
           customer_memo_line         customer_memo_line,
           x_bill_to_site_id        bill_to_site_id,
           x_set_of_books_id        set_of_books_id,
           x_org_id                     org_id,
           x_gl_cc_id            memo_cc_id,
           x_memo_line_id            memo_line_id,   
           x_amount            amount,
                x_comments                    comments
    from xyka_cash_debtor_recpt_hdr_v 
    where x_ar_transferred_flag='n'
    and x_receipt_source not in ('non - ar - yka','non - ar - property','non - ar - uet','non - ar - get','non - ar - aeo') --<>'non - ar - yka'
    and x_gl_transferred_flag <>'c'
        and x_date=p_receipt_date
        and x_receipt_source=p_receipt_source;       



/* to get applied invoices against receipts from debtor receipt *

    cursor ar_invoices(p_header_id number) is
     select x_line_id              line_id,
            x_header_id             header_id,
           x_customer_trx_number          trx_number,
           x_customer_trx_id          customer_trx_id,
           x_payment_schedule_id          payment_schedule_id,
           x_applied_amount        applied_amount,
           x_due_date            due_date,
           x_org_id            org_id
    from xyka_cash_debtor_inv_lines
        where x_header_id=p_header_id;


   --

   --

   --  define variables

   --
   t_user_id               number         := 0;
   t_org_id                number         := 0;
   t_login_id              number         := 0;
   t_req_id                number         := 0;       -- concurrent request id
   t_date                  date           := null;
   t_message               varchar2 (250) := null;
   tot_rec                 number         := 0;
   tot_err_rec             number         := 0;
   --

   --  define user specified parameters

   --
   lv_rec_count            number         := 1;
   lv_org_id               number (15)    := 0;
   lv_valid_cnt            number (15)    := 0;
   lv_null_cnt             number (15)    := 0;
   lv_chart_ok             boolean        := false;
   lv_curr                 varchar2 (15)  := null;
   lv_err_flag             varchar2 (1)   := 'n';
   l_batch_id               number         :=0;
   l_cash_receipt_id       number         :=0;
   l_batch_number       number;
   l_control_amt       number         :=0;
   l_control_count       number         :=0;
   l_set_of_books_id       number;
   l_org_id                number;
   l_site_use_id       number;
   l_receipt_line_id       number         :=1;
   l_batch_source_id       number;
   l_receipt_class_id       number;
   l_receipt_method_id       number;
   l_bank_branch_id       number;
   l_bank_account_id       number;
   l_customer_id       number;
   l_invoice_count       number:=0;
   l_source_id           number;
   l_amt_applied_from       number;
   l_discount_taken       number;
   l_currency_code     varchar2(100); --added by rohit
-- ---------------------------------------------------------------------
begin
   --

   -- get user id and login id

   --
   select fnd_global.user_id, fnd_global.login_id,fnd_profile.value('org_id')
     into t_user_id, t_login_id,t_org_id
     from dual;

   --

   --
   dbms_output.put_line
                      (   'program xykaardr (ar debtor receipts batch) started   at .....'
                       || to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss')
                      );

   --

   --

    for i in dr_batch
    loop


 /* to get batch sequence id *

     select ar_batches_s.nextval
        into l_batch_id
        from dual;


/* to get batch control amount and count *


        select sum(x_amount),
           count(*)
        into l_control_amt,
             l_control_count 
    from xyka_cash_debtor_recpt_hdr_v
    where x_ar_transferred_flag='n'
    and x_gl_transferred_flag <>'c'
    and x_receipt_source =i.x_receipt_source
    and x_date=i.x_date;



         if i.x_receipt_source='ar - yka' then

           l_source_id:=1002;
          
         elsif i.x_receipt_source = 'ar - uet' then -- added by rohit
         l_source_id := 1062;
        
         elsif i.x_receipt_source = 'ar - get' then -- added by rohit
         l_source_id := 1064; -- as per prod
         elsif i.x_receipt_source = 'ar - aeo' then -- added by rohit
         l_source_id := 1065; -- as per prod
        
         else
           l_source_id:=1001;
         end if;

    for s in batch_source(l_source_id)
     loop
            
         l_batch_source_id:=s.batch_source_id;
        l_receipt_class_id:=s.default_receipt_class_id;
        l_receipt_method_id:=s.default_receipt_method_id;
        l_bank_branch_id:=s.bank_branch_id;
        l_bank_account_id:=s.bank_account_id; 
         l_set_of_books_id:=s.set_of_books_id;
        l_org_id:=s.org_id;
         l_batch_number:=s.last_batch_num ;

     end loop;
         
         /* added by rohit *
       
         if (l_source_id = 1062 or  l_source_id = 1064) then
           l_currency_code:= 'aed';
         elsif l_source_id = 1065 then
           l_currency_code:= 'omr';
         else
           l_currency_code:= 'bhd';
         end if;
        /* added by rohit *
              
         insert into ar_batches_all
              (batch_id,
               last_updated_by,
           last_update_date,
           last_update_login,
           created_by,
           creation_date,
           name,
           batch_date,
           gl_date,
           status,
           deposit_date,
           type,
           batch_source_id,
           control_count,
           control_amount,
           batch_applied_status,
           currency_code,
           receipt_method_id,
           remittance_bank_account_id,
           receipt_class_id,
           remittance_bank_branch_id,
           set_of_books_id,
           org_id)
           values (l_batch_id,
           t_user_id,
                   sysdate,
                   t_login_id,
           t_user_id,
                   sysdate,
           l_batch_number,
           trunc(sysdate),
                   i.x_date,
           'op',
           i.x_date,
           'manual',
                   l_batch_source_id,
           l_control_count,       
                   l_control_amt,
           'postbatch_waiting',
           l_currency_code, -- added by rohit
                   l_receipt_method_id,
           l_bank_account_id,
           l_receipt_class_id,
           l_bank_branch_id,
                   l_set_of_books_id,
                   l_org_id);



    for j in debtor_receipts(i.x_date,i.x_receipt_source)
    loop

      select ar_cash_receipts_s.nextval
        into l_cash_receipt_id
        from dual;

      insert into ar_interim_cash_receipts_all
               (cash_receipt_id,
                last_updated_by,
            last_update_date,
            last_update_login,
            created_by,
            creation_date,
            amount,
            currency_code,
            batch_id,
            pay_from_customer,
            status,
            type,
            receipt_number,
            receipt_date,
            gl_date,
            special_type,
            receipt_method_id,
            remittance_bank_account_id,
            site_use_id,
                comments,
            org_id)
           values (l_cash_receipt_id,
           t_user_id,
                   sysdate,
                   t_login_id,
           t_user_id,
                   sysdate,
           j.amount,
           l_currency_code, -- added by rohit
                   l_batch_id,
           j.source_id,
           'unapp',
           'cash',
                   j.receipt_number,
           j.x_date,
                   j.x_date,
           'multiple',
           l_receipt_method_id,
                   l_bank_account_id,
           j.bill_to_site_id,
                   j.comments,
           l_org_id);  

     for l in ar_invoices(j.header_id)
     loop

       if l.trx_number='on account' then

           l_customer_id:=-1;
           l_amt_applied_from:=l.applied_amount;
           l_discount_taken  :=null; 

        else
           
        l_customer_id:=j.source_id;
        l_amt_applied_from:= null;
        l_discount_taken:=0;

        end if;

     insert into ar_interim_cash_rcpt_lines_all
               (cash_receipt_id,
                cash_receipt_line_id,
            last_updated_by,
            last_update_date,
            last_update_login,
            created_by,
            creation_date,
            payment_amount,
            payment_schedule_id,
            customer_trx_id,
            batch_id,
            sold_to_customer,
            discount_taken,
            due_date,
                amount_applied_from,
            org_id)
           values (l_cash_receipt_id,
                   l_receipt_line_id,
           t_user_id,
                   sysdate,
                   t_login_id,
           t_user_id,
                   sysdate,
           l.applied_amount,
           l.payment_schedule_id,
                   l.customer_trx_id,
           l_batch_id,
           l_customer_id,
           l_discount_taken,
                   l.due_date,
                   l_amt_applied_from,
           l_org_id);

     l_receipt_line_id:= l_receipt_line_id + 1;

          end loop;

update xyka_cash_debtor_recpt_hdr 
   set x_ar_transferred_flag='y'
   where x_header_id=j.header_id;

  commit;

        end loop;

  
 tot_rec := tot_rec +1;


 update ar_batch_sources_all
 set last_batch_num=l_batch_number+1
 where batch_source_id=l_batch_source_id;    --need to changed

end loop;

 commit;

--

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

   --
   <>                                        -- completion message
   commit;

   if tot_rec = 0
   then
      dbms_output.put_line ('there is no record for transfer.');
   else
      dbms_output.put_line
         (   'total records inserted for program xykaarri (open financials receipts batch)  ... :'
          || to_char (tot_rec)
         );
   end if;

   goto end_of_job;

   <>
   rollback;
   dbms_output.put_line (   'program xykaarri (open financials receipts batch) '
                         || to_char (sysdate, 'dd-mon-yyyy hh24:mi:ss')
                        );
   dbms_output.put_line (t_message);

   select to_date ('abc')                    -- just to simulate a fatal error
     into t_date
     from dual;

   goto end_of_job;

   <>
   null;                                                           -- all over
end;
/

-- exit;

-- -----------------------  end of script -----------------

--

OM - AR Drill Down

SELECT   Rl.Customer_Trx_Line_Id,
         Rl.Customer_Trx_Id,
         Rl.Line_Number,
         rh.trx_number,
         Rl.Interface_Line_Attribute1,
         H.Order_Number,
         L.Line_Id,
         Rl.Sales_Order_Line,
         rl.quantity_ordered,
         rl.quantity_invoiced,
         Rl.Extended_Amount
  FROM   Ra_Customer_Trx_Lines_All Rl,
         RA_CUSTOMER_TRX_ALL rh,
         oe_order_lines_all l,
         oe_order_headers_all h
 WHERE       line_type = 'LINE'
         AND interface_line_context = 'ORDER ENTRY'
         AND h.header_id = l.header_id
         AND interface_line_attribute6 = TO_CHAR (l.line_id)
         AND Interface_Line_Attribute1 = TO_CHAR (H.Order_Number)
         AND rh.customer_trx_id = rl.customer_trx_id
         AND sales_order = h.order_number
         and h.order_number = 13001107374

GL-AR Drilldown Function with Exception Handling

An Code combination account for a month contains balance; Balance is further divided into many generals; a general header is made up of many general lines; A line is made up of several receivables lines


Create or replace function xyka_gl_ar(x_je_header_id number ,x_je_line_num number, x_code_combination_id number)
 return varchar2   Is
          x_ret varchar2(100);   
          err_code varchar2(200);  
          err_msg    varchar2(200);     
  begin
 
  SELECT   -- gjb.name Batch_name ,
--           gjh.NAME Journal_name ,
--           gjh.JE_CATEGORY,
--           gjh.Je_source,
--           gjl.ENTERED_DR,
--           gjl.ENTERED_CR,
--           fnd_flex_ext.get_segs ('SQLGL',
--                                  'GL#',
--                                  gjb.chart_of_accounts_id,
--                                  gjl.code_combination_id)
--              ACCOUNT,
--           xla_oa_functions_pkg.get_ccid_description (gjb.chart_of_accounts_id,
--                                                      gjl.code_combination_id)
--              account_desc,
--           ract.trx_number,
           (SELECT   max(PARTY_NAME)
              FROM   hz_cust_accounts hca, hz_parties hp
             WHERE   hca.party_id = hp.party_id
                     AND hca.cust_account_id = ract.bill_TO_CUSTOMER_ID)
              Customer
    into  x_ret         
    FROM   gl_je_batches gjb,
           gl_je_headers gjh,
           gl_je_lines gjl,
           gl_code_combinations gcc,
           gl_import_References gir,
           xla_ae_lines xal,
           xla_distribution_links xdl,
           ra_customer_trx_all ract,
           ra_customer_trx_lines_all ractl,
           ra_cust_trx_line_gl_dist_all rctlgdl
   WHERE       GJH.PERIOD_NAME = 'NOV-13'
          -- AND gjb.name = 'YKCOST A 3348139' 
           -- gjb.DATE_CREATED LIKE SYSDATE
           --and gcc.code_combination_id = 619725
           and gjh.je_header_id = x_je_header_id --1370490
           AND gjl.JE_LINE_NUM = x_je_line_num
           AND gjh.je_header_id = gjl.je_header_id
           AND gjb.je_batch_id = gjh.je_batch_id
           AND gjL.je_header_id = gir.je_header_id
           AND Gjl.JE_LINE_NUM = gir.JE_LINE_NUM
           AND gir.je_batch_id = gjh.je_batch_id
           AND gcc.code_combination_id = gjl.CODE_COMBINATION_ID
           and gcc.code_combination_id = x_code_combination_id
           AND gjh.Je_source = 'Receivables'
           AND gjh.JE_CATEGORY = 'Sales Invoices'
          -- AND gjh.Je_source = 'Receivables'
           AND xal.ae_header_id = xdl.ae_header_id
           AND xal.GL_SL_LINK_ID = gir.gl_sl_link_id                 --2621350
           AND xal.GL_SL_LINK_table = gir.gl_sl_link_table
           AND SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
           AND xal.application_id = xdl.application_id
           AND xal.APPLICATION_ID = 222
           and gjh.LEDGER_ID = nvl(gjh.LEDGER_ID,gjh.LEDGER_ID)--:ledger_id --2021
           AND ractl.customer_trx_id = ract.customer_trx_id
           AND ractl.customer_trx_line_id = rctlgdl.customer_trx_line_id
           AND ract.customer_trx_id = rctlgdl.customer_trx_id
           AND xdl.source_distribution_id_num_1 =    rctlgdl.CUST_TRX_LINE_GL_DIST_ID
GROUP BY   gjb.name,
           gjh.NAME,
           gjh.JE_CATEGORY,
           gjh.Je_source,
           gjl.ENTERED_DR,
           gjl.ENTERED_CR,
           gjb.chart_of_accounts_id,
           gjl.code_combination_id,
           ract.trx_number,
           ract.bill_TO_CUSTOMER_ID;
          
    return(x_ret);
    Exception
         
         when Others then
          --return('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM));  
           --raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
           err_code := SQLCODE;
           err_msg := SUBSTR(SQLERRM, 1, 200);
           return(err_msg);
    end;
   
   

Pending RMA List

select ORDER_NUMBER ,
       (select ORGANIZATION_CODE from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_CODE,
        (select ORGANIZATION_name from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_name,
       oola.ORDERED_QUANTITY ,
       oola.FULFILLED_QUANTITY
from oe_order_headers_all ooha,
     oe_order_lines_all oola
where ooha.header_id = oola.header_id
and ooha.org_id = oola.org_id
and ORDER_CATEGORY_CODE = 'RETURN'
and oola.FLOW_STATUS_CODE = 'AWAITING_RETURN'
and oola.OPEN_FLAG = 'Y' --
order by 2
--and order_number = 3711800202

Item Reservations Query

SELECT   (select msib.segment1 || '|' || msib.segment2
         from mtl_system_items_b msib
         where msib.inventory_item_id = moq.inventory_item_id
               and msib.organization_id = moq.organization_id ) PART_NUMBER,
         moq.organization_id,
         ((SELECT   ORGANIZATION_code
                     FROM   org_organization_definitions odd
                    WHERE   organization_name LIKE 'UET%'
                            AND odd.organization_id = moq.organization_id)) Org_code,
        mr.reserve_quantity reserved_quantity,
         moq.transaction_quantity - mr.reserve_quantity available_toreserve_qty,
        moq.transaction_quantity        
  FROM   (  SELECT   moq.inventory_item_id inventory_item_id,
                     moq.organization_id organization_id,
                     SUM (moq.primary_transaction_quantity)
                        transaction_quantity
              FROM   mtl_onhand_quantities_detail moq
             WHERE   Moq.Organization_Id = nvl(:Organization_Id,Organization_Id)
                    -- AND moq.subinventory_code = 'XXXX'
          GROUP BY   moq.inventory_item_id, moq.organization_id) moq,
         (  SELECT   SUM (primary_reservation_quantity) reserve_quantity,
                     inventory_item_id,
                     organization_id
              FROM   Mtl_Reservations
             WHERE   Organization_Id = nvl(:Organization_Id,Organization_Id)
                   -- AND subinventory_code = ' XXXX'
          GROUP BY   Organization_Id, inventory_item_id) mr
 WHERE   moq.inventory_item_id = mr.inventory_item_id
         AND Moq.Organization_Id = Mr.Organization_Id
         and Moq.Organization_Id in (SELECT   ORGANIZATION_id
                                      FROM   org_organization_definitions odd
                                      WHERE   organization_name LIKE 'UET%')

INTERORG SHIPMENT Query

SELECT  
         ms.quantity,
         ms.receipt_date,
         ms.from_organization_id from_org,
         ms.to_organization_id to_org,
         ms.unit_of_measure uom,
         rsh.shipment_num,
         ms.item_id,
         mp1.organization_code from_org_code,
         mp2.organization_code to_org_code
    FROM
         mtl_supply ms,
         rcv_shipment_headers rsh,
         mtl_parameters mp1,
         mtl_parameters mp2
   WHERE
         ms.supply_type_code = 'SHIPMENT'
     AND ms.shipment_header_id = rsh.shipment_header_id
     AND (rsh.shipment_num = :p_shipment_no OR :p_shipment_no IS NULL)
     AND ms.from_organization_id = mp1.organization_id
     AND ms.to_organization_id = mp2.organization_id
ORDER BY receipt_date, mp2.organization_code

Business Groups / LE / OU / INV Query

Try the following ...
1) For Business Groups

select
o.organization_id ,
o.organization_id ,
otl.name ,
o.date_from ,
o.date_to ,
o.internal_address_line ,
o.location_id ,
o.comments ,
o2.org_information1 ,
o2.org_information2 ,
o2.org_information3 ,
o2.org_information4 ,
o3.org_information1 ,
o3.org_information2 ,
o3.org_information3 ,
o3.org_information4 ,
o3.org_information5 ,
o3.org_information6 ,
o3.org_information7 ,
o3.org_information8 ,
o3.org_information9 ,
o3.org_information10 ,
o3.org_information14 ,
o4.org_information2 ,
o3.org_information15 ,
o3.org_information16
from hr_all_organization_units o ,
hr_all_organization_units_tl otl ,
hr_organization_information o2 ,
hr_organization_information o3 ,
hr_organization_information o4
where o.organization_id = otl.organization_id
and o.organization_id = o2.organization_id (+)
and o.organization_id = o3.organization_id
and o.organization_id = o4.organization_id
and o3.org_information_context = 'Business Group Information'
and o2.org_information_context (+) = 'Work Day Information'
and o4.org_information_context = 'CLASS'
and o4.org_information1 = 'HR_BG'
and o4.org_information2 = 'Y'

2) For LEs :-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Legal Entity Accounting'
and o4.org_information1 = 'HR_LEGAL'

3) For OUs:-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Operating Unit Information'
and o4.org_information1 = 'OPERATING_UNIT'

4) For IOs:-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Accounting Information'
and o4.org_information1 = 'INV'

And finally, if you want to show OU, LE, SOB and SOB currency for your inv orgs, you can use this:

SELECT
o.organization_id
, otl.name
, o.date_from
, o.date_to
, o.internal_address_line
, loc.location_code as location
, o.comments
, les.business_group_id
, otl2.name as business_group
, les.organization_id as le_id
, les.name as legal_entity
, les.set_of_books_id
, sob.name as set_of_books
, sob.short_name
, sob.currency_code as currency
, opu.organization_id as ou_id
, opu.name as operating_unit
, les.vat_registration_number
FROM
hr_all_organization_units o
, hr_all_organization_units_tl otl
, hr_organization_information o2
, hr_organization_information o3
, hr_organization_information o4
, hr_legal_entities les
, hr_all_organization_units otl2
, hr_locations loc
, hr_operating_units opu
, gl_sets_of_books sob
where 1=1
and o.organization_id = otl.organization_id
AND o.organization_id = o2.organization_id (+)
and o.organization_id = o3.organization_id
and o.organization_id = o4.organization_id
AND o3.org_information_context = 'Accounting Information'
and o2.org_information_context (+) = 'Work Day Information'
and o4.org_information_context = 'CLASS'
and o4.org_information1 = 'INV'
and o4.org_information2 = 'Y'
and o3.org_information2 = les.organization_id
and les.business_group_id = otl2.organization_id
and o.location_id = loc.location_id(+)
and o3.org_information3 = opu.organization_id
and les.set_of_books_id = sob.set_of_books_id
order by o.organization_id

Payables Bank Acccount Update

Note -  Steps from Down to Up --
commit;

DECLARE
  CURSOR C1 IS SELECT * FROM NBB_EXT_ACC_ID;
BEGIN
  FOR C1_REC IN C1
   LOOP
   update pay_external_accounts pea set segment3 = C1_REC.ACCOUNT_NO where pea.external_account_id = C1_REC.EXTERNAL_ACCOUNT_ID;
   DBMS_OUTPUT.PUT_LiNE(C1_REC.ACCOUNT_NO || ' - ' ||C1_REC.EXTERNAL_ACCOUNT_ID );
  
 END LOOP;
END;


SELECT * FROM NBB_EXT_ACC_ID 

CREATE TABLE NBB_EXT_ACC_ID AS(
   Select EMPLOYEE_NUMBER,
         -- '0' || segment3 account_no ,
         (SELECT ACCOUNT_NO FROM NBB_ACCOUNT_INFO where EMP_NO = EMPLOYEE_NUMBER ) ACCOUNT_NO,
          pea.external_account_id
   FROM  per_all_people_f ppf,
         per_all_assignments_f paf,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea
   WHERE     pppm.external_account_id = pea.external_account_id
         AND ppf.person_id = paf.person_id
         AND pppm.assignment_id = paf.assignment_id
         AND pppm.business_group_id = ppf.business_group_id
         AND TRUNC (SYSDATE) BETWEEN ppf.effective_Start_Date
                                 AND  ppf.effective_end_date
         AND TRUNC (SYSDATE) BETWEEN paf.effective_start_Date
                                 AND  paf.effective_end_date
         AND TRUNC (SYSDATE) BETWEEN pppm.effective_start_date
                                 AND  pppm.effective_end_date
         AND paf.business_group_id = 81
         and ppf.EMPLOYEE_NUMBER IN (SELECT EMP_NO FROM NBB_ACCOUNT_INFO)
         and pppm.org_payment_method_id = 61 )

SELECT * FROM NBB_Account_info

create table NBB_ACCOUNT_INFO
( EMP_NO VARCHAR2(5) , ACCOUNT_NO VARCHAR2(15) ) ;

INSERT INTO NBB_Account_info VALUES ('154454556','0266448895');
INSERT INTO NBB_Account_info VALUES ('154545968','0273123564');
INSERT INTO NBB_Account_info VALUES ('115445440','0274278774');

Update Bank Account Button Disabled

Update Bank Account Button Disabled ... Dont worry !!



1. Login as System Administrator user  (SYSADMIN User) and then switch responsibility to User Management. ( If responsibility is not added pls add same )
2. Click in Roles & Role Inheritance link
3. Do the following search: - Type: Role and Responsibilities - Category: Miscellaneous - Application: Cash Management Choose the Cash Management responsibility where you want to create and ma intain bank accounts.
4. Click in the Update icon 5. Click in Security Wizard button
6. Run the Wizard for CE UMX Security wizard.
7. Click in Add Legal entities and add the legal entity you will give the selected role access to the all bank accounts within this legal entity and choose grants that you want to assign to this role on the bank accounts of this legal entity ( Use, Maintenance, Bank Account Transfers )
8. Click in the Apply button and then click in the save button.
9. Now try to create or update a bank account in the chosen Cash Management Responsibility
---end of note

GL - XLA - AR Drill Down Query

/* GL - XLA - AR Drill Down Query */ 


SELECT   gjb.name,
         gjh.doc_sequence_value,
         gjl.ENTERED_DR,
         gjl.ENTERED_CR,
         (   gcc.segment1
          || '-'
          || gcc.segment2
          || '-'
          || gcc.segment3
          || '-'
          || gcc.segment4
          || '-'
          || gcc.segment5
          || '-'
          || gcc.segment6
          || '-'
          || gcc.segment7
          || '-'
          || gcc.segment8)
            Account,
         (SELECT   TRX_NUMBER
            FROM   xla_ae_lines xal,
                   xla_distribution_links xdl,
                   ra_customer_trx_all ract,
                   ra_customer_trx_lines_all ractl,
                   ra_cust_trx_line_gl_dist_all rctlgdl
           WHERE   xal.ae_header_id = xdl.ae_header_id
                   AND xal.GL_SL_LINK_ID = gir.gl_sl_link_id         --2621350
                   AND SOURCE_DISTRIBUTION_TYPE =  'RA_CUST_TRX_LINE_GL_DIST_ALL'
                   AND xal.application_id = xdl.application_id
                   AND xal.APPLICATION_ID = 222
                   AND ractl.customer_trx_id = ract.customer_trx_id
                   AND ractl.customer_trx_line_id =
                         rctlgdl.customer_trx_line_id
                   AND ract.customer_trx_id = rctlgdl.customer_trx_id
                   AND xdl.source_distribution_id_num_1 =
                         rctlgdl.CUST_TRX_LINE_GL_DIST_ID)
            TRX_NUMBER,
         gir.SUBLEDGER_DOC_SEQUENCE_VALUE
  FROM   gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl,
         gl_code_combinations gcc,
         gl_import_References gir
 WHERE       gjb.je_batch_id = gjh.je_batch_id
         AND gjh.je_header_id = gjl.je_header_id
         AND gcc.code_combination_id = gjl.CODE_COMBINATION_ID
         AND gir.je_header_id = gjh.je_header_id
         AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
         AND gir.je_batch_id = gjh.je_batch_id
         AND gjb.DATE_CREATED LIKE SYSDATE