Assignment_Set_Creation_API

Assignment_Set_Creation_API


--Assignment_Set_Creation_API
DECLARE
   l_payroll_id            NUMBER := 61;
   l_assignment_set_name   VARCHAR2 (500) := 'XXPK_ASSIGNMENT_SET_API';
   out_assignment_set_id     NUMBER := NULL;
BEGIN


   BEGIN
      hr_jp_ast_utility_pkg.
       create_asg_set (p_assignment_set_name   => l_assignment_set_name,
                       p_business_group_id     => 81,
                       p_payroll_id            => l_payroll_id,
                       p_assignment_set_id     =>  out_assignment_set_id );
      COMMIT;

      DBMS_OUTPUT.put_line ( out_assignment_set_id  || ' has been Created Successfully !!!');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

Workflow Item Type And Workflow Item Key Wise Approval History Find SQL

--Workflow Item Type And Workflow Item Key Wise Approval History Find SQL

SELECT ROWNUM h_sequence, h_notification_id, h_from_role, h_from_user,
       h_to_role, h_to_user, h_action, h_comment, h_action_date,
       activity_status, activity_result_code,item_type,item_key
  FROM (SELECT   h_notification_id, h_from_role, h_from_user, h_to_role,
                 h_to_user, h_action, h_comment, h_action_date,
                 activity_status, activity_result_code,item_type,item_key
            FROM (SELECT 99999999 h_sequence,
                         ias.notification_id h_notification_id,
                         ias.assigned_user h_from_role,
                         wf_directory.getroledisplayname2
                                               (ias.assigned_user)
                                                                  h_from_user,
                         'WF_SYSTEM' h_to_role,
                         wf_core.TRANSLATE ('WF_SYSTEM') h_to_user,
                         wf_core.activity_result
                                           (a.result_type,
                                            ias.activity_result_code
                                           ) h_action,
                         (SELECT text_value
                            FROM wf_notification_attributes
                           WHERE notification_id =
                                                ias.notification_id
                             AND NAME = 'WF_NOTE') h_comment,
                         NVL (to_char(ias.end_date,'DD-MON-YYYY'), to_char(ias.begin_date,'DD-MON-YYYY')) h_action_date,
                         ias.activity_status, ias.activity_result_code,ias.item_type,ias.item_key
                    FROM wf_item_activity_statuses ias,
                         wf_activities a,
                         wf_process_activities pa,
                         wf_items i
                   WHERE ias.item_type LIKE '%'||:P_ITEM_TYPE||'%'-- 'XXSUPREQ'--'XXMPRFWF' --item_type
                     AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1--tem_key
                     --AND ias.process_activity = :process_activity
                     AND ias.notification_id IS NOT NULL --added
                     AND ias.item_type = i.item_type
                     AND ias.item_key = i.item_key
                     AND ias.activity_result_code IS NOT NULL
                     AND ias.activity_result_code NOT IN
                            ('#EXCEPTION',
                             '#FORCE',
                             '#MAIL',
                             '#NULL',
                             '#STUCK',
                             '#TIMEOUT'
                            )
                     AND i.begin_date BETWEEN a.begin_date
                                          AND NVL (a.end_date, i.begin_date)
                     AND ias.process_activity = pa.instance_id
                     AND pa.activity_name = a.NAME
                     AND pa.activity_item_type = a.item_type
                  UNION ALL
                  SELECT 99999999 h_sequence,
                         ias.notification_id h_notification_id,
                         ias.assigned_user h_from_role,
                         wf_directory.getroledisplayname2
                                               (ias.assigned_user)
                                                                  h_from_user,
                         'WF_SYSTEM' h_to_role,
                         wf_core.TRANSLATE ('WF_SYSTEM') h_to_user,
                         wf_core.activity_result
                                           (a.result_type,
                                            ias.activity_result_code
                                           ) h_action,
                         (SELECT text_value
                            FROM wf_notification_attributes
                           WHERE notification_id =
                                                ias.notification_id
                             AND NAME = 'WF_NOTE') h_comment,
                         NVL (to_char(ias.end_date,'DD-MON-YYYY'), to_char(ias.begin_date,'DD-MON-YYYY')) h_action_date,
                         ias.activity_status, ias.activity_result_code,ias.item_type,ias.item_key
                    FROM wf_item_activity_statuses_h ias,
                         wf_activities a,
                         wf_process_activities pa,
                         wf_items i
                   WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'--'XXMPRFWF' --item_type
                     AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
                     --AND ias.process_activity = :process_activity
                     AND ias.notification_id IS NOT NULL --added
                     AND ias.item_type = i.item_type
                     AND ias.item_key = i.item_key
                     AND ias.activity_result_code IS NOT NULL
                     AND ias.activity_result_code NOT IN
                            ('#EXCEPTION',
                             '#FORCE',
                             '#MAIL',
                             '#NULL',
                             '#STUCK',
                             '#TIMEOUT'
                            )
                     AND i.begin_date BETWEEN a.begin_date
                                          AND NVL (a.end_date, i.begin_date)
                     AND ias.process_activity = pa.instance_id
                     AND pa.activity_name = a.NAME
                     AND pa.activity_item_type = a.item_type
                  UNION ALL
                  SELECT c.SEQUENCE h_sequence,
                         c.notification_id h_notification_id,
                         c.from_role h_from_role, c.from_user h_from_user,
                         c.to_role h_to_role, c.to_user h_to_user,
                         wf_core.TRANSLATE (c.action) h_action,
                         c.user_comment h_comment,
                         to_char(c.comment_date,'DD-MON-YYYY') h_action_date, ias.activity_status,
                         ias.activity_result_code,ias.item_type,ias.item_key
                    FROM wf_item_activity_statuses ias, wf_comments c
                   WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'-- 'XXMPRFWF' --item_type
                     AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
                     --AND ias.process_activity = :process_activity
             AND ias.notification_id IS NOT NULL --added
                     AND ias.notification_id = c.notification_id
                     AND c.action NOT IN
                            ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
                  UNION ALL
                  SELECT c.SEQUENCE h_sequence,
                         c.notification_id h_notification_id,
                         c.from_role h_from_role, c.from_user h_from_user,
                         c.to_role h_to_role, c.to_user h_to_user,
                         wf_core.TRANSLATE (c.action) h_action,
                         c.user_comment h_comment,
                         to_char(c.comment_date,'DD-MON-YYYY') h_action_date, ias.activity_status,
                         ias.activity_result_code,ias.item_type,ias.item_key
                    FROM wf_item_activity_statuses_h ias, wf_comments c
                   WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'--'XXMPRFWF' --item_type
                     AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
                     --AND ias.process_activity = :process_activity
                     AND ias.notification_id IS NOT NULL --added
                     AND ias.notification_id = c.notification_id
                     AND c.action NOT IN
                            ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND'))
        ORDER BY  h_notification_id, h_sequence)
 WHERE h_action NOT IN ('Yes', 'No', 'Cancel')
   AND NVL (activity_result_code, 'X') NOT LIKE '%NULL%'

AR Open Receipt Apply to other Receipt by ar_receipt_api_pub.Apply_Open_Receipt API Example

AR Open Receipt Apply to other Receipt by ar_receipt_api_pub.Apply_Open_Receipt API Example

=======================================================================
DECLARE
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
v_r2r_return_status         VARCHAR2 (1);
v_r2r_msg_count             NUMBER;
v_r2r_msg_data              VARCHAR2 (240);
lv_application_ref_num          ar_receivable_applications.application_ref_num%TYPE;
lv_receivable_application_id   ar_receivable_applications.receivable_application_id%TYPE;
lv_applied_rec_app_id            ar_receivable_applications.receivable_application_id%TYPE;
lv_acctd_amount_applied_from     ar_receivable_applications.acctd_amount_applied_from%TYPE;
lv_acctd_amount_applied_to       ar_receivable_applications.acctd_amount_applied_to%TYPE;

cursor c_open_rcpts (cp_cus VARCHAR2) IS


SELECT DISTINCT aou.NAME operating_unit, hp.party_name customer_name,
                hca.account_number customer_number, arca.receipt_number,
                arca.cash_receipt_id,
                (g.amount_due_remaining * NVL (g.exchange_rate, 1)
                ) amount_due_remaining,
                arca.org_id
         /*,
crh_first_posted.gl_date gl_date, crh_first_posted.gl_date trx_date, arca.status,
    arca.TYPE receipt_type, arca.currency_code, arca.receipt_number,
    arca.receipt_date, hp.party_name received_from,
    hca.account_number customer_no, arca.amount entered_amount,
    crh_current.acctd_amount functional_amount,
    -1 * (crh_current.amount) total_net_amount, arm.NAME payment_method,
    apbb.bank_name bank_account_name,
    apbaa.bank_account_num bank_account_num, arca.comments description*/
           FROM hz_parties hp,
                hz_cust_accounts_all hca,
                ar_cash_receipts_all arca,
                ar_receipt_methods arm,
                ar_receipt_classes arc,
                ap_bank_accounts_all apbaa,
                ap_bank_branches apbb,
                ar_cash_receipt_history_all crh_first_posted,
                ar_cash_receipt_history_all crh_current,
                hr_all_organization_units aou,
                ar_payment_schedules_all g
--       ,hz_party_sites hps,
--       hz_cust_site_uses_all hcsua,
--       hz_cust_acct_sites_all hcas
WHERE           1 = 1
            AND arca.org_id = aou.organization_id(+)
--   AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
--   AND hps.party_site_id = hcas.party_site_id
--   and arca.CUSTOMER_SITE_USE_ID=hcsua.BILL_TO_SITE_USE_ID
            AND arca.cash_receipt_id = 9882804              --9882805--9882804
            AND NVL (arca.status, 'ZZ') <> 'APP'                   --Open Only
--   AND hca.ACCOUNT_NUMBER=cp_cus
            AND arca.pay_from_customer = hca.cust_account_id(+)
            AND hp.party_id(+) = hca.party_id
            AND arca.remittance_bank_account_id = apbaa.bank_account_id(+)
            AND apbb.bank_branch_id(+) = apbaa.bank_branch_id
            AND arca.receipt_method_id = arm.receipt_method_id
            AND arm.receipt_class_id = arc.receipt_class_id
--      AND crh_first_posted.batch_id = bat.batch_id(+)
--      AND crh_first_posted.org_id = bat.org_id(+)
--      AND bat.TYPE(+) = 'MANUAL'
            AND crh_first_posted.cash_receipt_id(+) = arca.cash_receipt_id
            AND crh_first_posted.org_id(+) = arca.org_id
            AND crh_first_posted.first_posted_record_flag(+) = 'Y'
--      AND crh_first_posted.batch_id = bat_br.batch_id(+)
--      AND crh_first_posted.org_id = bat_br.org_id(+)
            AND crh_current.cash_receipt_id = arca.cash_receipt_id
            AND crh_current.org_id = arca.org_id
            AND crh_current.current_record_flag =
                                                NVL ('Y', arca.receipt_number)
--   and crh_first_posted.gl_date between to_date('01-JAN-2019') and to_date('31-DEC-2019')
--and to_char(crh_first_posted.gl_date,'YYYY')='2019'
            AND arca.org_id = 103 ;--MTC
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
BEGIN

      fnd_global.apps_initialize (0,
                                  52219,--MTC Receivables Administrator
                                  222,--Receivables
                                  103
                                 );
      mo_global.set_policy_context ('S', 103);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts 
--For yz in   c_open_rcpts () loop 
--RECEIPT-A :=>To which the open RECEIPT have to apply
   ar_receipt_api_pub.apply_open_receipt
                (
                 -- Standard API parameters.
                 p_api_version                      => 1.0,
                 p_init_msg_list                    => apps.fnd_api.g_true,
                 p_commit                           => apps.fnd_api.g_true,
                 p_validation_level                 => apps.fnd_api.g_valid_level_full,--100,
                 x_return_status                    => v_r2r_return_status,
                 x_msg_count                        => v_r2r_msg_count,
                 x_msg_data                         => v_r2r_msg_data,
                 --  Receipt application parameters.
                 p_cash_receipt_id                  => 9882805,--To which the open RECEIPT have to apply
                 p_receipt_number                   => 'RECEIPT-A', --To which the open RECEIPT have to apply
                 p_applied_payment_schedule_id      => NULL,
                 p_open_cash_receipt_id             => 9882804,--Non Zero Amount Cursor Rcpt
                 p_open_receipt_number              => 'RECEIPT-OPEN' ,--Non Zero Amount Cursor Rcpt
                 p_open_rec_app_id                  => null,--NONE,
                 p_amount_applied                   => -20000,--Non Zero Amount Cursor Rcpt Amount
                 p_apply_date                       => TRUNC (SYSDATE),
                 p_apply_gl_date                    => TRUNC (SYSDATE),
                 p_ussgl_transaction_code           => null,--NONE,
                 p_attribute_rec                    => NULL,
              -- ******* Global Flexfield parameters *******   
                 p_global_attribute_rec             => null,--NONE,
                 p_comments                         => 'Reciept to reciept Application by API',
                 p_org_id                           =>103,
                 x_application_ref_num          =>lv_application_ref_num,
                x_receivable_application_id    =>lv_receivable_application_id,
                x_applied_rec_app_id          =>lv_applied_rec_app_id,
                x_acctd_amount_applied_from   =>lv_acctd_amount_applied_from,
                x_acctd_amount_applied_to     =>lv_acctd_amount_applied_to
                );
              Commit; 
--     end loop;         
dbms_output.put_line('v_r2r_msg_data :-'||v_r2r_msg_data);               
dbms_output.put_line('x_application_ref_num :-'||lv_application_ref_num);
dbms_output.put_line('x_receivable_application_id :-'||lv_receivable_application_id);
dbms_output.put_line('x_applied_rec_app_id :-'||lv_applied_rec_app_id);
dbms_output.put_line('x_acctd_amount_applied_from :-'||lv_acctd_amount_applied_from);
dbms_output.put_line('x_acctd_amount_applied_to :-'||lv_acctd_amount_applied_to);               
dbms_output.put_line('API Completed Successfully :-'||SQLERRM);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
-- commit;
exception when others then
dbms_output.put_line('Main Exception :-'||SQLERRM);
END;

GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package

GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package
==========
CREATE OR REPLACE PACKAGE APPS.XX_RCV_TRX_GENERIC_PKG AS
/*  Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

   FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
                        p_shipment_line_id number)
                        return number;
                     
 /*  Following function to get the quantiy of grn line by transaction type wise like ACCEPT,REJECT,CORRECT,Deliver*/
 --      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual                     
    FUNCTION trx_actual_qty (
   p_shipment_header_id   NUMBER,
   p_shipment_line_id     NUMBER,
   p_transaction_type     VARCHAR2
) RETURN NUMBER;
END XX_RCV_TRX_GENERIC_PKG;
/

======================================
CREATE OR REPLACE PACKAGE BODY APPS.XX_RCV_TRX_GENERIC_PKG AS

/*  Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
                        p_shipment_line_id number)
   RETURN NUMBER IS

   
      /* corsor for recive transactions */
      cursor c1 is
     select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
     rt1.currency_code
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
        and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
        and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
        AND rt1.transaction_type = 'RECEIVE';
     
      /* Cursor c1 to calculate tax amount*/
      cursor c2(p_parent_tran_id number) is
      select rt1.TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
     rt1.currency_code
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
        and rt1.PARENT_TRANSACTION_ID=p_parent_tran_id--111606
        and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
        and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
        and rt1.TRANSACTION_TYPE='CORRECT';
        v_tax_amt_f number :=0;
        v_tax_amt number:=0;
        v_base_tax_amt number:=0;
        v_tax_amt_c number:=0;
     
   BEGIN

   
      for i in c1 loop
   
      SELECT   jai_rcv_trx_processing_pkg.get_trxn_tax_amount
                                                  (i.TRANSACTION_ID,
                                                   p_shipment_line_id,
                                                  i.CONVERSION_RATE,
                                                   i.CURRENCY_CODE
                                                  )
                                   into v_base_tax_amt
                                 from dual;
   
   
             for j in c2(i.TRANSACTION_ID)loop
                     SELECT   jai_rcv_trx_processing_pkg.get_trxn_tax_amount
                                                  (j.TRANSACTION_ID,
                                                   p_shipment_line_id,
                                                  j.CONVERSION_RATE,
                                                   j.CURRENCY_CODE
                                                  )
                                   into v_tax_amt
                                 from dual;
             v_tax_amt_c:=v_tax_amt_c+v_tax_amt;
           
             end loop;
   
      v_tax_amt_f:=v_base_tax_amt+v_tax_amt_c;
      end loop;
   
      /*  select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,rsh1.RECEIPT_NUM,
     rt1.currency_code,grn.GRN_TAX_AMT(rsh1.SHIPMENT_HEADER_ID,rsl1.SHIPMENT_LINE_ID ) tax_amount,rsl1.LINE_NUM
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
       -- and rt1.SHIPMENT_HEADER_ID=48874
        --and rt1.SHIPMENT_LINE_ID=61233
        AND rt1.transaction_type = 'RECEIVE'
        order by rsh1.RECEIPT_NUM,rsl1.LINE_NUM*/
   
      return v_tax_amt_f;
   exception
   when others then
    return (-1);
         
   END GRN_LINE_IL_TAX_AMT;
--      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual 
   FUNCTION trx_actual_qty (
   p_shipment_header_id   NUMBER,
   p_shipment_line_id     NUMBER,
   p_transaction_type     VARCHAR2)
   RETURN NUMBER
IS
   v_qty                NUMBER:=0;
   v_qty_f              NUMBER;
   v_transaction_type   VARCHAR2 (30);
   v_transaction_id     NUMBER;
   v_count number;
   v_routing_name varchar2(30);
   c_qty number:=0;
ret_rcv_qty number:=0;
v_correct_qty number:=0;
v_rtv_qty number:=0;

   CURSOR c1
   IS
      SELECT rt.*
        FROM rcv_shipment_headers rsh,
             rcv_shipment_lines rsl,
             rcv_transactions rt
       WHERE rsh.shipment_header_id = rsl.shipment_header_id
         AND rsl.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND rsh.shipment_header_id =
                         p_shipment_header_id
                                             --rsh.RECEIPT_NUM='5102140004389'
         AND rt.shipment_line_id = p_shipment_line_id                  --61178
         AND rt.transaction_type = p_transaction_type;
BEGIN
   v_qty_f := 0;

   FOR r1 IN c1
   LOOP
 
 
     
                            begin
                              SELECT count(1)
                              into v_count
                              FROM rcv_transactions rt
                              WHERE rt.parent_transaction_id = r1.transaction_id;
                            exception
                              when no_data_found then
                               v_count:=0;                         
                            end;
                         
         if v_count>0 then
     
       
          FOR i IN (SELECT rt.transaction_type, rt.transaction_id,rt.QUANTITY
                  FROM rcv_transactions rt
                 WHERE rt.parent_transaction_id = r1.transaction_id)
       
              LOOP
             
                 
                         
                         
                            if i.transaction_type='RETURN TO RECEIVING'then
                         
                         
                               
                                 ret_rcv_qty:=ret_rcv_qty+(-1*i.QUANTITY);
                         
                            elsif i.transaction_type='CORRECT' then
                         
                            v_correct_qty:=v_correct_qty+i.QUANTITY;
                            elsif i.transaction_type='RETURN TO VENDOR' then
                            v_rtv_qty:=v_rtv_qty+(-1*i.QUANTITY);
                            end if;
               
               
           
             
              end loop;
              v_qty:=nvl(ret_rcv_qty,0)+nvl(v_correct_qty,0)+nvl(v_rtv_qty,0)+nvl(r1.QUANTITY,0);
         elsif v_count=0 then
       
         v_qty:=r1.QUANTITY;
       
          end if;
           
     
 

      v_qty_f := v_qty;--+r1.quantity;
   END LOOP;                                              --cursor c1 loop end

   RETURN v_qty_f;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN (-1);
   
--      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual
END trx_actual_qty;


END XX_RCV_TRX_GENERIC_PKG;
/

Sample MDS repository Path of Seeded(Standard) OAF Pages

--Path of standard (seeded) absence path

/appltest/appl/apps/apps_st/appl/per/12.0.0/mds/absences/absencecase/webui