Query for Getting Profile option value by user level, Site level, resposibilty level

SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
and f.user_profile_option_name in ('AP: Use Invoice Batch Controls')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;

API to Delete Concurrent Program

Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;

AR Receipt Creation API

CREATE OR REPLACE PROCEDURE APPS.xxar_receipt_creation_api (
   errbuf    OUT varchar2,
   retcode   OUT varchar2
--P_FROM_DATE       varchar2,
--P_TO_DATE         varchar2
)
IS
   l_return_status         VARCHAR2 (1);
   l_msg_count             NUMBER;
   l_msg_data              VARCHAR2 (1000);
   l_cr_id                 NUMBER;
   V_STATUS                number := 0; --------0 SHOW THAT SUCCCESS BUT 1 SHOWS THAT EXCEPTION OCCUR-------
   V_RECEIPT_METHOD_NAME   VARCHAR2 (500);
   V_receipt_method_id     NUMBER;
   --V_ORG_ID := FND_PROFILE.VALUE('ORG_ID');
   V_CUSTOMER_ID           RA_CUSTOMERS.CUSTOMER_ID%TYPE;
   V_CUSTOMER_NAME         RA_CUSTOMERS.CUSTOMER_NAME%TYPE;
   V_CUSTOMER_NUMBER       RA_CUSTOMERS.CUSTOMER_NUMBER%TYPE;
   V_RECEIPT_COUNT         NUMBER;
   V_SITE_USE_ID           hz_cust_site_uses_all.SITE_USE_ID%TYPE;
   V_CURRENCY_CODE         VARCHAR2 (120);
   V_BANK_ACCOUNT_ID       NUMBER;
   V_BANK_ACCOUNT_NAME     VARCHAR2 (500);
   V_EXCHNAGE_RATE         NUMBER;
   V_EXCHNAGE_DATE         DATE;
   V_EXCHNAGE_TYPE         VARCHAR2 (200);
   P_ATTRIBUTE_REC         AR_RECEIPT_API_PUB.attribute_rec_type;
   p_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type;
   p_doc_sequence_value    number;
   p_cr_id                 NUMBER;
  
   --LP_FROM_DATE  date :=      FND_DATE.CANONICAL_TO_DATE(P_FROM_DATE);
   --LP_TO_DATE  date  :=   FND_DATE.CANONICAL_TO_DATE(P_TO_DATE);
   CURSOR c
   IS
        SELECT   RECEIPT_ID,
                 RECEIPT_NUMBER,
                 AMOUNT,
                 RECEIVED_DATE,
                 GL_DATE,
                 CURRENCY_CODE,
                 AGENT_CODE,
                 BANK_BRANCH_ID,
                 EXCHANGE_RATE,
                 REMARKS,
                 SOURCE,
                 ADDL_FIELD5,
                 BANK_CHARGES
          FROM   OA_AR_RECEIPT_MAS@TAPROD_DBLINK IRDB
         --WHERE RECEIPT_NUMBER='HUZ1202000230';
         WHERE   RECEIPT_NUMBER NOT IN
                       (SELECT   RECEIPT_NUMBER
                          FROM   AR_CASH_RECEIPTS_ALL A
                         WHERE   A.RECEIPT_NUMBER = IRDB.RECEIPT_NUMBER)
                 AND IRDB.TYPE = 'Customer'
                 AND NVL (IRDB.CANCEL_FLAG, 'N') = 'N'
                 AND IRDB.RECEIPT_NUMBER = 'HUZ1304000199'
      --AND IRDB.ENTERED_DATE < TRUNC(SYSDATE)
      --AND IRDB.GL_DATE BETWEEN LP_FROM_DATE AND LP_TO_DATE
      ORDER BY   BANK_BRANCH_ID;
BEGIN
   ---------------------------------Intililize org ------------------
      FND_GLOBAL.APPS_INITIALIZE (FND_PROFILE.VALUE ('USER_ID'),
                               FND_PROFILE.VALUE ('RESP_ID'),
                               FND_PROFILE.VALUE ('RESP_APPL_ID'),
                               NULL,
                               NULL);
   V_STATUS := 0;

   FOR i IN c
   LOOP
      /***********************Validation start for receipt creation**********************/

      -------------receipt method validation---------

      IF I.SOURCE IS NOT NULL
      THEN
         BEGIN
            SELECT   name, receipt_method_id
              INTO   V_RECEIPT_METHOD_NAME, V_receipt_method_id
              FROM   ar_receipt_methods
             WHERE   INITCAP (SUBSTR (NAME, 6, 4)) = INITCAP (I.SOURCE)
                     AND end_date IS NULL;
         EXCEPTION
            WHEN OTHERS
            THEN
               FND_FILE.PUT_LINE (
                  FND_FILE.LOG,
                     'INVALID RECEIPT METHOD FOR GIVEN RECEIPT NUMBER'
                  || ' '
                  || I.RECEIPT_NUMBER
               );
                DBMS_output.put_line('INVALID RECEIPT METHOD FOR GIVEN RECEIPT NUMBER'
                  || ' '
                  || I.RECEIPT_NUMBER);
                  V_STATUS := 1;
         END;
      ELSE
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
               ' RECEIPT METHOD CAN NOT BE NULL FOR GIVEN RECEIPT NUMBER'
            || '  '
            || I.RECEIPT_NUMBER
         );
         V_STATUS := 1;
      END IF;
     
    

      ------------------CUSTOMER NUMBER, CUSTOME ID VALIDATION ----------------
      BEGIN
         SELECT   CUSTOMER_ID, CUSTOMER_NUMBER, CUSTOMER_NAME
           INTO   V_CUSTOMER_ID, V_CUSTOMER_NUMBER, V_CUSTOMER_NAME
           FROM   RA_CUSTOMERS
          WHERE   UPPER (CUSTOMER_NUMBER) = UPPER (I.AGENT_CODE)
                  AND STATUS = 'A';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.PUT_LINE('INVALID CUSTOMER NAME OR CUSTOMER DOES NOT EXISTS IN ORACLE APPS FOR'
                                 || '  '
                                 || I.RECEIPT_NUMBER);
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (
               'INVALID CUSTOMER NAME' || '  ' || I.RECEIPT_NUMBER
            );
            V_STATUS := 1;
      END;
      -----------------VALIDATION FOR CUST SITE USE ID---------------------
      BEGIN
         SELECT   site_use_id
           INTO   V_SITE_USE_ID
           FROM   Ra_CUSTOMERS a,
                  hz_cust_Acct_sites_all b,
                  hz_cust_site_uses_all c
          WHERE       a.customer_id = b.cust_account_id
                  AND b.cust_acct_site_id = c.cust_acct_site_id
                  AND A.CUSTOMER_ID = V_CUSTOMER_ID;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (
               'INVALID CUSTOMER SITE USE ID FOR' || '  ' || I.RECEIPT_NUMBER
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'INVALID CUSTOMER SITE USE ID FOR' || '  ' || I.RECEIPT_NUMBER
            );
            V_STATUS := 1;
      END;

      -------------------VALIDATION FOR RECEIPT NUMBER---------------
      IF I.RECEIPT_NUMBER IS NOT NULL
      THEN
         BEGIN
            SELECT   COUNT ( * )
              INTO   V_RECEIPT_COUNT
              FROM   AR_CASH_RECEIPTS_ALL
             WHERE   RECEIPT_NUMBER = I.RECEIPT_NUMBER
                     AND PAY_FROM_CUSTOMER = V_CUSTOMER_ID;

            IF V_RECEIPT_COUNT > 0
            THEN
               DBMS_OUTPUT.PUT_LINE(   'RECEIPT NUMBER ALREADY EXISTS FOR'
                                    || '  '
                                    || I.RECEIPT_NUMBER);
               FND_FILE.PUT_LINE (
                  FND_FILE.LOG,
                     'RECEIPT NUMBER ALREADY EXISTS FOR'
                  || ' '
                  || I.RECEIPT_NUMBER
               );
               V_STATUS := 1;
            END IF;
         END;
      ELSE
         DBMS_OUTPUT.PUT_LINE (
            'RECEIPT NUMBER CAN NOT BE NULL' || '  ' || I.RECEIPT_NUMBER
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'RECEIPT NUMBER CAN NOT BE NULL' || '  ' || I.RECEIPT_NUMBER
         );
         V_STATUS := 1;
      END IF;

      ---------------------------VALIDATION FOR CURRENCY CODE------------------
      BEGIN
         SELECT   CURRENCY_CODE
           INTO   V_CURRENCY_CODE
           FROM   FND_CURRENCIES
          WHERE       CURRENCY_CODE = I.CURRENCY_CODE
                  AND ENABLED_FLAG = 'Y'
                  AND END_DATE_ACTIVE IS NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE('INVALID CURRENCY CODE FOR RECEIPT NUMBER'
                                 || '  '
                                 || I.RECEIPT_NUMBER);
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
                  'INVALID CURRENCY CODE FOR RECEIPT NUMBER'
               || '  '
               || I.RECEIPT_NUMBER
            );
            v_status := 1;
      END;

      ------------------VALIDATION FOR EXCHNAGE RATE , DATE-------------
      IF I.CURRENCY_CODE = 'INR'
      THEN
         V_EXCHNAGE_RATE := NULL;
         V_EXCHNAGE_DATE := NULL;
         V_EXCHNAGE_TYPE := NULL;
      ELSE
         NULL;
      END IF;

      ------------------VALIDATION FOR BANK ACCOUNT-------------------
      BEGIN
         SELECT   BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME
           INTO   V_BANK_ACCOUNT_ID, V_BANK_ACCOUNT_NAME
           FROM   AP_BANK_ACCOUNTS_ALL
          WHERE   BANK_BRANCH_ID = I.BANK_BRANCH_ID;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE('Bank Branch Does not exists so please check it'
                                 || '  '
                                 || I.RECEIPT_NUMBER);
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
                  'Bank Branch Does not exists so please check it'
               || '  '
               || I.RECEIPT_NUMBER
            );
      END;

      /********************* End of validation **********************/

      /*--------CALLING API FOR CREATING RECEIPT ------------------*/

      IF v_status = 0
      THEN
         BEGIN
            DBMS_OUTPUT.PUT_LINE ('1');
            AR_RECEIPT_API_PUB.Create_cash (
               P_API_VERSION                  => 1.0,
               P_INIT_MSG_LIST                => FND_API.G_FALSE,
               P_COMMIT                       => FND_API.G_FALSE,
               P_VALIDATION_LEVEL             => FND_API.G_VALID_LEVEL_FULL,
               P_CURRENCY_CODE                => V_CURRENCY_CODE,
               p_exchange_rate_type           => V_EXCHNAGE_TYPE,
               p_exchange_rate                => V_EXCHNAGE_RATE,
               p_exchange_rate_date           => V_EXCHNAGE_DATE,
               p_amount                       => (NVL (I.AMOUNT, 0)),                                                 
               p_receipt_number               => I.RECEIPT_NUMBER,
               p_receipt_date                 => I.GL_DATE,
               p_gl_date                      => I.GL_DATE,
               p_customer_id                  => V_CUSTOMER_ID,
               p_remittance_bank_account_id   => V_BANK_ACCOUNT_ID,
               p_receipt_method_id            => V_RECEIPT_METHOD_ID,
               p_doc_sequence_value           => p_doc_sequence_value,
               p_attribute_rec                => p_attribute_rec,
               p_global_attribute_rec => p_global_attribute_rec,
               p_comments                     => I.REMARKS,
               x_return_status                => l_return_status,
               x_msg_count                    => l_msg_count,
               x_msg_data                     => l_msg_data,
               p_cr_id                        => p_cr_id
            );
            DBMS_OUTPUT.PUT_LINE ('1.1');

            ------------CHECKING API STATUS IS IT SUCCESS OR NOT-----------
            IF l_return_status = 'S'
            THEN
               COMMIT;
               DBMS_OUTPUT.PUT_LINE (
                  'RECEIPT CREATED SUCCESSFULLY' || I.RECEIPT_NUMBER
               );
               FND_FILE.PUT_LINE (
                  FND_FILE.LOG,
                  'RECEIPT CREATED SUCCESSFULLY' || I.RECEIPT_NUMBER
               );
               FND_FILE.PUT_LINE (FND_FILE.LOG,
                                  'RETURN STATUS' || l_return_status);
               FND_FILE.PUT_LINE (FND_FILE.LOG,
                                  'MESSAGE COUNT' || l_msg_count);
               FND_FILE.PUT_LINE (FND_FILE.LOG, 'MESSAGE DATA' || l_msg_data);
            ELSE
               ROLLBACK;
               FND_FILE.PUT_LINE (FND_FILE.LOG,
                                  'RETURN STATUS' || '  ' || l_return_status);
               FND_FILE.PUT_LINE (FND_FILE.LOG,
                                  'MESSAGE COUNT' || '  ' || l_msg_count);
               FND_FILE.PUT_LINE (FND_FILE.LOG,
                                  'MESSAGE DATA' || ' ' || l_msg_data);
               DBMS_OUTPUT.put_line ('Message count' || '   ' || l_msg_count);
               DBMS_OUTPUT.put_line (
                  'Return Status ' || '    ' || l_return_status
               );

               --DBMS_OUTPUT.put_line ('Return Messgae Status ' || '    '  || l_msg_data);

               ------------PRITING ERROR LOG FILE ------------
               IF l_msg_count = 1
               THEN
                  DBMS_OUTPUT.put_line ('ERROR MESSGAE: ' || I.RECEIPT_NUMBER || '  ' || l_msg_data);
               ELSIF l_msg_count > 1
               THEN
                  LOOP
                     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(   'ERROR MESSGAE:-'
                                          || '  '
                                          || I.RECEIPT_NUMBER
                                          || '  '
                                          || l_msg_data);
                  END LOOP;
               END IF;      -----------END OF PRITNING ERROR LOG FILE---------
            END IF;         ------------END OF CHECKING API STATUS -----------
         END;
      END IF; --------------END OF CALLING API FOR CREATING RECEIPT-----------------
   END LOOP;     ----------------------END OF MAIN LOOP-----------------------
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('EXCEPTIONS:' || ' ' || SQLERRM);
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'EXCEPTIONS:' || '  ' || SQLERRM);
END;
/