SQL Query Process

/* Formatted on 8/19/2014 9:14:59 AM (QP5 v5.115.810.9015) */
V_USER_ID                          NUMBER;
MENU_ID                            MENUITEM;
MENU_ID1                            MENUITEM;

BEGIN
   v_user_id   := NAME_IN ('USER.USER_ID');
   menu_id     := FIND_MENU_ITEM ('SPECIAL_C.SPECIAL42');
   menu_id1    := FIND_MENU_ITEM ('SPECIAL_C.SPECIAL45');

   IF v_user_id IS NULL
   THEN
      SET_MENU_ITEM_PROPERTY (menu_id, enabled, property_false);
      SET_MENU_ITEM_PROPERTY (menu_id1, enabled, property_false);
   ELSE
      SET_MENU_ITEM_PROPERTY (menu_id, enabled, property_true);
      SET_MENU_ITEM_PROPERTY (menu_id1, enabled, property_true);
   END IF;
END;


/* Formatted on 8/19/2014 9:16:22 AM (QP5 v5.115.810.9015) */
v_variable     varchar2(30);

BEGIN
   NULL;

   IF (NAME_IN ('SYSTEM.RECORD_STATUS') = 'INSERT')
   THEN
      fnd_message.set_string('End date is defaulted to inactivate the responsibility. To activate the responsibility, navigate to Actions>Activate Responsibilities');
      fnd_message.show;
   END IF;
END;

Basic Code

  SUBMIT_REQUEST
====================
DECLARE

 P_ID1   VARCHAR2(30);
 P_ID2   NUMBER;
 ln_request_id           NUMBER          := 0;
 LC_PERIOD_NAME     VARCHAR2(30);
 LN_APPLICATION_ID NUMBER;
 LN_LEDGER_ID  NUMBER;
 LC_LEDGER_SHORT_NAME VARCHAR2(30);

BEGIN

 P_ID1    := #DISPOSITION_ID1#;
 P_ID2    := #DISPOSITION_ID2#;

SELECT  GPS.ATTRIBUTE2
        ,GPS.ATTRIBUTE3
        ,GPS.ATTRIBUTE4
        ,GL.SHORT_NAME
INTO   LC_PERIOD_NAME    
           ,LN_APPLICATION_ID
           ,LN_LEDGER_ID
           ,LC_LEDGER_SHORT_NAME
FROM    LA_EXTENSION_VALUES  GPS
        ,GL_LEDGERS GL
WHERE   GPS.ATTRIBUTE4=GL.LEDGER_ID
AND     GPS.ATTRIBUTE2=P_ID1
AND     GPS.ATTRIBUTE3=101
AND     GPS.ATTRIBUTE4=P_ID2;

                    ln_request_id:=fnd_request.submit_request ('SQLGL',
                                                'GLOOAP',
                                                'Open Period',
                                                SYSDATE,
                                                FALSE, --TRUE,   --sub_request
                                                LC_LEDGER_SHORT_NAME,  --Ledger/Ledger Set
                                                1017,  --Access Set ID
                                                LN_LEDGER_ID,  --Ledger ID
                                                101,  --Chart Of Accounts ID
                                                LN_APPLICATION_ID,  --Application ID
                                                'P',  -- Execution Mode
                                                LC_PERIOD_NAME    -- Period Name
                                               );
                 COMMIT;

END;


===============================================================
select name from gl_ledgers
where ledger_id = gl_access_set_security_pkg.get_default_ledger_id
(FND_PROFILE.VALUE('GL_ACCESS_SET_ID'),'F');

===============================================================
DECLARE
   P_ID1                            number:=#DISPOSITION_ID1#;
   v_validate                       BOOLEAN := FALSE;
   v_attribute20                    VARCHAR2 (100) := NULL;
   v_valid_grades_changed_warning   BOOLEAN;

   CURSOR c1
   IS
      SELECT position_id, object_version_number, position_definition_id, name
      FROM per_all_positions
      WHERE position_id = P_ID1;
BEGIN
   FOR a IN c1
   LOOP
      hr_position_api.update_position (p_validate => v_validate,
                                       p_position_id => a.position_id,
                                       p_object_version_number => a.object_version_number,
                                       p_attribute20 => v_attribute20,
                                       p_position_definition_id => a.position_definition_id,
                                       p_name => a.name,
                                       p_valid_grades_changed_warning => v_valid_grades_changed_warning
      );
   END LOOP;
END;


==================================================================
/* Formatted on 8/13/2014 8:41:17 AM (QP5 v5.115.810.9015) */
DECLARE
   p_id1   NUMBER := #disposition_id1#;

   CURSOR c1
   IS
      SELECT ROWID,
             org_information_id,
             org_information_context,
             organization_id,
             org_information1,
             org_information10,
             org_information11,
             org_information12,
             org_information13,
             org_information14,
             org_information15,
             org_information16,
             org_information17,
             org_information18,
             org_information19,
             org_information2,
             org_information20,
             org_information3,
             org_information4,
             org_information5,
             org_information6,
             org_information7,
             org_information8,
             org_information9,
             attribute_category,
             attribute1,
             attribute2,
             attribute3,
             attribute4,
             attribute5,
             attribute6,
             attribute7,
             attribute8,
             attribute9,
             attribute10,
             attribute11,
             attribute12,
             attribute13,
             attribute14,
             attribute15,
             attribute16,
             attribute17,
             attribute18,
             attribute19,
             attribute20
      FROM hr_organization_information
      WHERE     organization_id = p_id1
            AND org_information1 = 'HR_ORG'
            AND org_information_context = 'CLASS';
BEGIN
   FOR a IN c1
   LOOP
      a.org_information2   := 'Y';
      hr_org_information_pkg.update_row (a.ROWID,
                                         a.org_information_id,
                                         a.org_information_context,
                                         a.organization_id,
                                         a.org_information1,
                                         a.org_information10,
                                         a.org_information11,
                                         a.org_information12,
                                         a.org_information13,
                                         a.org_information14,
                                         a.org_information15,
                                         a.org_information16,
                                         a.org_information17,
                                         a.org_information18,
                                         a.org_information19,
                                         a.org_information2,
                                         a.org_information20,
                                         a.org_information3,
                                         a.org_information4,
                                         a.org_information5,
                                         a.org_information6,
                                         a.org_information7,
                                         a.org_information8,
                                         a.org_information9,
                                         a.attribute_category,
                                         a.attribute1,
                                         a.attribute2,
                                         a.attribute3,
                                         a.attribute4,
                                         a.attribute5,
                                         a.attribute6,
                                         a.attribute7,
                                         a.attribute8,
                                         a.attribute9,
                                         a.attribute10,
                                         a.attribute11,
                                         a.attribute12,
                                         a.attribute13,
                                         a.attribute14,
                                         a.attribute15,
                                         a.attribute16,
                                         a.attribute17,
                                         a.attribute18,
                                         a.attribute19,
                                         a.attribute20
      );
   END LOOP;
END;

=============================================================

declare
xx number;
begin

/* Formatted on 8/13/2014 8:42:23 AM (QP5 v5.115.810.9015) */
UPDATE xxogs_ap_sup
SET new_terms_id   =
       (SELECT terms_id
        FROM ap_suppliers
        WHERE vendor_id = #disposition_id1#
              AND enabled_flag = #disposition_id2#),
    val_flag   = 'T'
WHERE     vendor_id = #disposition_id1#
      AND enabled_flag = #disposition_id2#
      AND val_flag IS NULL;
     
commit;


/* Formatted on 8/13/2014 8:42:40 AM (QP5 v5.115.810.9015) */

UPDATE xxogs_ap_sup
SET new_terms_id = NULL, val_flag = NULL
WHERE     vendor_id = #disposition_id1#
      AND enabled_flag = #disposition_id2#
      AND val_flag = 'T'
      AND terms_id = NVL (new_terms_id, terms_id);
     
commit;

end;


=================================================================

DECLARE
   p_id1        NUMBER;
   p_id2        NUMBER;
   r_order_id   NUMBER;
   r_resp_id    NUMBER;
   opmsg        VARCHAR2 (2000);
   errbuf       VARCHAR2 (2000);
   retcode      VARCHAR2 (2000);
BEGIN
   p_id1 := #DISPOSITION_ID1#;
   p_id2 :=#DISPOSITION_ID2#;

   BEGIN
      SELECT order_type_id
        INTO r_order_id
        FROM oe_order_headers_all
       WHERE header_id = p_id1;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         INSERT INTO grc_om_test
              VALUES (p_id1, p_id2, 'No Transaction Type Found');
   END;

   BEGIN
      SELECT a.responsibility_id
        INTO r_resp_id
        FROM fnd_login_responsibilities a, fnd_logins b
       WHERE a.login_id = b.login_id
         AND b.user_id = (SELECT created_by
                            FROM oe_order_headers_all
                           WHERE header_id = p_id1)
         AND b.login_type = 'FORM'
         AND a.resp_appl_id = 660
         AND a.start_time IN (SELECT MAX (start_time)
                                FROM fnd_login_responsibilities
                               WHERE resp_appl_id = 660);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         INSERT INTO grc_om_test
              VALUES (p_id1, p_id2, 'No Resp Found');
   END;

   INSERT INTO grc_om_test
        VALUES (p_id1, p_id2, r_order_id);
   INSERT INTO grc_om_test
        VALUES (p_id1, p_id2, r_resp_id);  
   IF r_order_id=4181 and r_resp_id=52300 THEN
    INSERT INTO grc_om_test
         VALUES (p_id1, p_id2, 'PCG START');

   sales_order_proc (p_id1, p_id2, opmsg, errbuf, retcode);

   INSERT INTO grc_om_test
        VALUES (p_id1, p_id2, opmsg);

   COMMIT;

   INSERT INTO grc_om_test
        VALUES (p_id1, p_id2, 'PCG END');
   END IF;    

   COMMIT;
END;




Pending and Running   

DECLARE

P_ID1   number:=#DISPOSITION_ID1#;
P_ID2   number:=#DISPOSITION_ID2#;

BEGIN

UPDATE
fnd_concurrent_requests
SET phase_code = 'P',
status_code = 'I'
WHERE
REQUEST_ID = P_ID1
AND CONCURRENT_PROGRAM_ID = P_ID2;

COMMIT;

END;

Complete Cancel

DECLARE

 P_ID1   number:=#DISPOSITION_ID1#;
 P_ID2   number:=#DISPOSITION_ID2#;

BEGIN

UPDATE fnd_concurrent_requests
SET PHASE_CODE='C'
,STATUS_CODE='D'
Where REQUEST_ID = P_ID1
 and CONCURRENT_PROGRAM_ID = P_ID2;

COMMIT;

END;

Concurrent Hold Process Trigger

CREATE OR REPLACE TRIGGER  < TRIGGER_NAME >
   BEFORE INSERT
   ON FND_CONCURRENT_REQUESTS
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
WHEN (
NEW.concurrent_program_id = 41819
      )
DECLARE
   v_key   NUMBER;
BEGIN
   --:NEW.hold_flag   := 'Y';
   :NEW.status_code := 'Z';
END;
/

CREATE OR REPLACE TRIGGER < Trigger_Name >
   BEFORE INSERT
   ON FND_CONCURRENT_REQUESTS
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
WHEN (
NEW.concurrent_program_id = 109075
      )
DECLARE
   v_key          NUMBER;
   v_created_by   VARCHAR2 (100);
BEGIN
   SELECT created_by
     INTO v_created_by
     FROM ap_suppliers
    WHERE vendor_id IN (SELECT payee_supplier_id
                          FROM iby_payments_all
                         WHERE payment_instruction_id = :NEW.argument1);

   IF :NEW.requested_by = v_created_by
   THEN
      :NEW.status_code := 'A';
   END IF;
END;

Host file Path in system

C:\Windows\System32\drivers\etc