Lookups , Value sets, Profile Options,Request Sets

SELECT * FROM FND_LOOKUP_TYPES_VL WHERE LOOKUP_TYPE LIKE 'SIDDHARTH_TEST%'


SELECT * FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE LIKE 'SIDDHARTH_TEST%'-- Go With this query this help in finding the lookupcode and Meaning.....


Profile Option
===========

SELECT FND_PROFILE.VALUE('XXX_profile') FROM DUAL

How to do it...

To create a request set using the wizard complete the following tasks:

1. 1. Log in to Oracle with the System Administrator responsibility.
2. 2. Navigate to Requests | Set and the Request Set window will open, as shown in the following screenshot:
   
3. 3. Click the Request Set Wizard button.
4. 4. Select the radio button called Sequentially (One After Another) and then click Next, as shown in the following screenshot:
   
5. 5. Click on radio button called Continue Processing. This is what we want the request set to do if any of the programs end with a status of Error:
   
6. 6. We now need to enter the details of our request set in the wizard as shown in the following table and click Next:

   Item name	Item value
   Set	XXHR20001
   Application	XXHR Custom Application
   Description	XXHR Employee By Organization

   
7. 7. We now want to print the output files as each request finishes, so select As Each Request in the Set Completes and click Next as follows:
   
8. 8. Now add the concurrent programs that we want to run in the request set so add the two programs we have created, XXHR First Concurrent Program and XXHR Second Concurrent Program, and click Finish as shown in the following screenshot:
   
9. 9. The following message will appear; click OK:
   
10. 10. The request set is then automatically created and the completed set will appear something similar to the following screenshot. We are going to first look at the Define Stages screen and the Link Stages screen to check the configuration:
    
11. 11. Click on the Define Stages button to check that the concurrent programs are configured as required:
    
12. 12. Check that the screen is configured as we expected and then close the Stages window to navigate back to the Request Set window.
13. 13. Click on the Link Stages button and the Link Stages window will open as shown in the following screenshot:
    

How it works...

We have now created a request set using the request set wizard. We can now run the request set, and the concurrent programs will run as we have defined them in the request set.

Add a request set to a request group

We will now add our request set to the request group we have associated with the XXEBS Extending e-Business Suite responsibility.

How to do it...

To add the request set perform the following steps:

1. 1. Log in to Oracle with the System Administrator responsibility.
2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open.
3. 3. Query back the XXHR Request Group request group.
4. 4. Now we are going to add the request set we created in the Requests region. Enter data as in the following table in the Requests block:

   Type	Name	Application
   Set	XXHR20001	XXHR Custom Application

5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.
6. 6. Exit the form.

How it works...

Okay so now we have added the request set to our request group. Next we are going to run the request set.

Run the request set

Now we want to run the concurrent request set.

How to do it...

To run the request set take the following steps:

1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.
2. 2. Navigate to Submit Requests and click the OK button as shown in the following screenshot:
   
3. 3. Navigate to the Request Set field and select the XXHR20001 request set from the list of values and click OK.
4. 4. Click on the Submit button and when prompted to submit a new request select No and the form will close down.
5. 5. Navigate to View Requests and click on the Find button (to find all requests) and you will see the request set as shown in the following screenshot:
   
6. 6. You should see that the request set we just submitted is running.

If you click the refresh button you will see the stages of the request set complete as they are executed. Once the request set has completed you will see three records in the Requests block. One for the set and one each for the concurrent programs in the request set as shown in the following screenshot:

Note

Note: Remember to click the refresh button as the page does not refresh automatically, so if you see a program still has a phase of Running you will need to click the refresh button until the phase is Completed.

How it works...

We have now run the request set and can see that we can

VALUE SETS
============

Profile Option===========

Outbound Interfacing with Exceptions

CREATE OR REPLACE PROCEDURE OUTBOUND_INTERFACE(ERRBUF VARCHAR2, RETCODE VARCHAR2) IS
CURSOR C1 IS SELECT VENDOR_NAME,VENDOR_ID,VENDOR_TYPE_LOOKUP_CODE FROM PO_VENDORS;
V_OUTFILE                  UTL_FILE.FILE_TYPE;
V_OUTPATH                  VARCHAR2(100);
V_OUTFILENAME              VARCHAR2(200);
V_HEADER                   VARCHAR2(400);
V_LINE                     VARCHAR2(4000);
BEGIN
FND_PROFILE.GET ('CMG_ABC_INCOMING_PATH', V_OUTPATH);
V_OUTFILENAME :='ENS_EXCEL_FEED' || '.csv';
V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');
V_HEADER :='VENDOR_NAME'||','||'VENDOR_ID'||','||'VENDOR_TYPE_LOOKUP_CODE';
UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
FOR UPC_REC IN C1 LOOP
V_LINE:=UPC_REC.VENDOR_NAME||','||UPC_REC.VENDOR_ID||','||UPC_REC.VENDOR_TYPE_LOOKUP_CODE;
UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);
END LOOP;
UTL_FILE.FCLOSE (V_OUTFILE);
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');
END;
/



/HDGSTG01/APPLR11I/COMMON/ABC/INCOMING
SELECT FND_PROFILE.VALUE('CMG_ABC_INCOMING_PATH') FROM DUAL

Merge Statement

MERGE INTO CMG_PPA_SUBSIDIARY_LEDGER_TBL  hra
USING (
     select AMOUNT,CMG_PPA_SUB_LEDGER_ID SUB from
       CMG_PPA_SUBSIDIARY_LEDGER_TBL
         where vendor_id=143) main1
ON (hra.CMG_PPA_SUB_LEDGER_ID =main1.SUB)
WHEN MATCHED THEN
UPDATE SET
hra.VENDOR_func_amount = main1.AMOUNT
WHEN NOT MATCHED THEN
INSERT ( hra.VENDOR_func_amount) VALUES (main1.AMOUNT);
Commit;


MERGE INTO CMG_PPA_REMITTANCES_DETAIL_TBL  hra
USING (
     select AMOUNT,REMITTANCE_DETAIL_ID RDI from
            CMG_PPA_REMITTANCES_DETAIL_TBL
         where issue_dim_id in (select issue_dim_id from CMGPPA.CMG_PPA_ISSUE_DIM_TBL where ISS_PUB_ID=143))main1
ON (hra.REMITTANCE_DETAIL_ID=main1.RDI)
WHEN MATCHED THEN
UPDATE SET
hra.CMG_FUNC_AMOUNT = main1.AMOUNT
WHEN NOT MATCHED THEN
INSERT ( hra.CMG_