Query For Drop More Than One Constraint Using Procedure

                             Query For Drop More Than One Constraint Using Procedure


CREATE OR REPLACE PROCEDURE KBR_CONSTRAINT_DROP(L_TABLE_NAME VARCHAR2)
AS
L_ROWNUM NUMBER;
L_CONSTRA VARCHAR2(50);
L_OBJECT_NAME VARCHAR2(50);
L_CONSTRATS VARCHAR2(30);
BEGIN
BEGIN
L_ROWNUM :=1;
L_CONSTRATS :=' CONSTRAINT';
END;
BEGIN
FOR KBR IN 1..5
LOOP
L_ROWNUM :=1;
SELECT CONSTRAINT_NAME INTO L_CONSTRA
 FROM user_cons_columns
WHERE table_name =L_TABLE_NAME 
AND COLUMN_NAME IN ('LAST_UPDATE_DATE' ,'LAST_UPDATED_BY','CREATION_DATE','CREATED_BY','LAST_UPDATE_LOGIN')AND ROWNUM=L_ROWNUM;
EXECUTE IMMEDIATE 'ALTER TABLE '|| L_TABLE_NAME|| ' DROP '||L_CONSTRATS||' '||L_CONSTRA;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
END;



EXEC KBR_CONSTRAINT_DROP('XXHRS_EMP_DATA')

XML Publisher Report from XML Data Template(SQL Query)

                        XML Publisher Report from XML Data Template(SQL Query)

<?xml version="1.0"?><dataTemplate version="1.0" name="XXHRCUSTOMER"><properties></properties><parameters><parameter name="p_customer_id" datatype="NUMBER"> </parameter></parameters><dataQuery><sqlStatement name="XXHRCUSTSLT"><![CDATA[SELECT DISTINCT  (HP.PARTY_ID) CUSTOMER_ID,HCP.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,HP.PARTY_NAME CUSTOMER_NAME,ARCPV.profile_class_name Profile_Name,(HP.ADDRESS1||','||HP.ADDRESS2||','||HP.ADDRESS3||','||HP.ADDRESS4 ||','||HP.CITY||','||HP.STATE||','||HP.COUNTRY||','||HP.POSTAL_CODE)ADDRESS, HP.PRIMARY_PHONE_NUMBER CONTACT_NUMBER,HP.EMAIL_ADDRESS EMAIL,HP.URL CUST_WEBSITE,HCA.ACCOUNT_NAME ACCOUNT_NAME,HCP.DISCOUNT_TERMS DISCOUNT_TERMS,HCP.DUNNING_LETTERS DUNNING_LETTERS,HCP.INTEREST_CHARGES INTEREST_CHARGES,HRS.OBJECT_TYPE OBJECT_TYPE,HPP.PERSON_NAME PERSON_NAME,IEPA.PAYMENT_FUNCTION PAYMENT_FUNCTION,IPIUA.INSTRUMENT_TYPE PAYMENT_TYPE,IEBA.BANK_ACCOUNT_NAME HOLDER_NAME,IEBA.BANK_ACCOUNT_NUM ACCOUNT_NUMBER,IEBA.CURRENCY_CODE BANK_ACCEPT_CURRENCY,HLS.CITY BILL_TO,HLS.CITY SHIP_TOFROM HZ_PARTIES HP,hz_party_sites hps,HZ_LOCATIONS HLS,HZ_CUST_ACCOUNTS HCA,HZ_CUSTOMER_PROFILES HCP,AR_CUSTOMER_PROFILE_CLASSES_V ARCPV,HZ_RELATIONSHIPS HRS,hz_person_profiles HPP,IBY_EXTERNAL_PAYERS_ALL IEPA,IBY_PMT_INSTR_USES_ALL IPIUA,IBY_EXT_BANK_ACCOUNTS IEBAwhere HP.PARTY_ID=HCA.PARTY_IDAND HP.PARTY_ID=HPS.PARTY_IDAND HPS.LOCATION_ID=HLS.LOCATION_IDAND HCA.CUST_ACCOUNT_ID=HCP.CUST_ACCOUNT_IDAND HCP.COLLECTOR_ID=ARCPV.COLLECTOR_IDAND HCA.PARTY_ID=HRS.SUBJECT_IDAND HRS.OBJECT_ID=HPP.PARTY_IDAND HCA.CUST_ACCOUNT_ID=IEPA.CUST_ACCOUNT_IDAND IEPA.EXT_PAYER_ID=IPIUA.EXT_PMT_PARTY_IDAND IPIUA.INSTRUMENT_ID=IEBA.EXT_BANK_ACCOUNT_ID(+)AND HP.party_id=:p_customer_id ]]></sqlStatement></dataQuery><dataStructure><group name="XXHRCUSTSLT" datatype="varchar2" source="XXHRCUSTSLT"><element name="CUSTOMER_ID" value="CUSTOMER_ID"/><element name="CUST_ACCOUNT_ID" value="CUST_ACCOUNT_ID"/><element name="CUSTOMER_NAME" value="CUSTOMER_NAME"/><element name="Profile_Name" value="Profile_Name"/><element name="ADDRESS" value="ADDRESS"/><element name="CONTACT_NUMBER" value="CONTACT_NUMBER"/><element name="EMAIL" value="EMAIL"/><element name="CUST_WEBSITE" value="CUST_WEBSITE"/><element name="ACCOUNT_NAME" value="ACCOUNT_NAME"/><element name="DISCOUNT_TERMS" value="DISCOUNT_TERMS"/><element name="DUNNING_LETTERS" value="DUNNING_LETTERS"/><element name="INTEREST_CHARGES" value="INTEREST_CHARGES"/><element name="OBJECT_TYPE" value="OBJECT_TYPE"/><element name="PERSON_NAME" value="PERSON_NAME"/><element name="PAYMENT_FUNCTION" value="PAYMENT_FUNCTION"/><element name="PAYMENT_TYPE" value="PAYMENT_TYPE"/><element name="HOLDER_NAME" value="HOLDER_NAME"/><element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/><element name="BANK_ACCEPT_CURRENCY" value="BANK_ACCEPT_CURRENCY"/><element name="BILL_TO" value="BILL_TO"/><element name="SHIP_TO" value="SHIP_TO"/></group></dataStructure></dataTemplate>

Concurrent Program WAIT until completion of another Concurrent Program

To make Concurrent Program WAIT until completion of another Concurrent Program:

CREATE OR REPLACE PROCEDURE apps.xxorc_test_backs (
   p_errbuff
               OUT   VARCHAR2,
   p_retcode
               OUT   VARCHAR2,
   f_arg1            NUMBER
                           ,
   s_arg1            VARCHAR2
                             ,
   s_arg2            VARCHAR2
                             ,
   s_arg3            VARCHAR2
                             ,
   s_arg4            VARCHAR2
                             ,
   s_arg5            VARCHAR2


)
AS
   req_id                NUMBER;


   RESULT                BOOLEAN;
   lv_request_id         NUMBER;


   lc_phase              VARCHAR2 (50);
   lc_status             VARCHAR2 (50);
   lc_dev_phase          VARCHAR2 (50);
   lc_dev_status         VARCHAR2 (50);
   lc_message            VARCHAR2 (50);
   l_req_return_status   BOOLEAN;
BEGIN
   fnd_global.apps_initialize (1013497, 50554, 200)

                                                   ;
   RESULT := fnd_request.set_mode (TRUE)

                                        ;
   req_id :=
      fnd_request.submit_request (application      => 'AR',
                                  program          => 'CPRXLS',
                                  start_time       => TO_CHAR (SYSDATE,
                                                               'DD-MON-YYYY'
                                                              ),
                                  sub_request      => FALSE,
                                  argument1        => f_arg1
                                 )

                                  ;
   COMMIT;

   IF lv_request_id = 0
   THEN
      DBMS_OUTPUT.put_line (   'Request Not Submitted due to "'
                            || fnd_message.get

                            || '".'
                           );
   ELSE
      DBMS_OUTPUT.put_line
         (   'The Program VIVEKPROGRAM_1 submitted successfully – Request id :'
          || req_id
         );
   END IF;

   IF req_id > 0
   THEN
      LOOP
         l_req_return_status :=
            fnd_concurrent.wait_for_request
               (request_id      => req_id,
                INTERVAL        => 5
                           --interval Number of seconds to wait between checks
                                    ,
                max_wait        => 60
                --Maximum number of seconds to wait for the request completion
                                     -- out arguments
            ,
                phase           => lc_phase,
                status          => lc_status,
                dev_phase       => lc_dev_phase,
                dev_status      => lc_dev_status,
                MESSAGE         => lc_message
               )

                ;
         EXIT WHEN UPPER (lc_phase) = 'COMPLETED'
               OR UPPER (lc_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
      END LOOP;

      --

      --
      IF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'ERROR'
      THEN
         DBMS_OUTPUT.put_line
               (   'The XX_PROGRAM_1 completed in error. Oracle request id: '
                || req_id
                || ' '
                || SQLERRM
               );
      ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL'
      THEN
         DBMS_OUTPUT.put_line
                   (   'The XX_PROGRAM_1 request successful for request id: '
                    || req_id
                   );

         --
         BEGIN
            fnd_global.apps_initialize (1013497, 50554, 200)

                                                            ;
            RESULT := fnd_request.set_mode (TRUE)

                                                 ;
            lv_request_id :=
               fnd_request.submit_request
                                       (application      => 'AR',
                                        program          => 'XXTFS',
                                        -- description   => 'VIVEK_PROGRAM_2',
                                        start_time       => TO_CHAR
                                                                (SYSDATE,
                                                                 'DD-MON-YYYY'
                                                                ),
                                        sub_request      => FALSE,
                                        argument1        => s_arg1,
                                        argument2        => s_arg2,
                                        argument3        => s_arg3,
                                        argument4        => s_arg4,
                                        argument5        => s_arg5
                                       )

                                        ;
            
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                    (   'OTHERS exception while submitting VIVEK_PROGRAM_2: '
                     || SQLERRM
                    );

         END;
      ELSE
         DBMS_OUTPUT.put_line
                   (   'The XX_PROGRAM_1 request failed. Oracle request id: '
                    || req_id
                    || ' '
                    || SQLERRM
                   );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                    (   'OTHERS exception while submitting VIVEK_PROGRAM_1: '
                     || SQLERRM
                    );
      COMMIT;

END
   ;

Any Queries Mail Me vivekexpertize@gmail.com

Copy From One directory to another directory In oracle

                            ----Copy From One directory to another directory -----

Two method To Transfer  :

Step 1: Utl_File.Frename

CREATE OR REPLACE DIRECTORY XXHRS_SOURCES_DIR AS '/u01/E-BIS/inst/apps/VISION/logs/appl/conc/out'

CREATE OR REPLACE DIRECTORY XXHRS_TARGET_DIR AS '/HOME/VIVEK'

CREATE OR REPLACE PROCEDURE APPS.XXORC_TRANSFER(ERRBUFF VARCHAR2,RETCODE NUMBER,P_REQUEST_ID NUMBER)
AS
L_FILE_NAME  VARCHAR2(80);
l_format VARCHAR2(60);
BEGIN
SELECT (SUBSTR
         (OUTFILE_NAME,
            INSTR
               (OUTFILE_NAME,
                '/',
                -1
               )
          + 1
         )) INTO L_FILE_NAME
FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=P_REQUEST_ID;
SELECT (SUBSTR(L_FILE_NAME,1,(INSTR(L_FILE_NAME,'.'))-1)||'.xls') into l_format FROM DUAL;
UTL_FILE.FRENAME ('SOURCES_OUT_DIR',
L_FILE_NAME,
'TARGET_DIR',
l_format,
TRUE);
END;

Step 2: Shell Script

Shell Script Code---Click Here to View Sample Code

#Shell Script Code Change Ur Code As Per Ur requiremnet
P_SOURCE_FILE="$5"
P_SOURCE_DIR="$6"
P_TARGET_FILE="$7"
P_TARGET_DIR="$8"
P_TRANSFER_MODE="$9"
SrcFilePath=$P_SOURCE_DIR/$P_SOURCE_FILE
TgtFilePath=$P_TARGET_DIR/$P_TARGET_FILE
#ensure the file exists
if [ ! -f $SrcFilePath ]; then
  echo "ERROR: Source file does not exist: $SrcFilePath" >> $P_LOGFILE
  exit 40
fi
#ensure the target direcotry exists
if [ ! -d $P_TARGET_DIR ]; then
  echo "ERROR: Target directory does not exist: $P_TARGET_DIR"   >> $P_LOGFILE
  exit 50
fi
# copy and rename the file
if [ "$P_TRANSFER_MODE" = "COPY" ]; then
  cp $SrcFilePath $TgtFilePath
elif [ "$P_TRANSFER_MODE" = "MOVE" ]; then
  mv $SrcFilePath $TgtFilePath
else
  echo "ERROR: Invalid transfer type specified: $P_TRANSFER_MODE" >> $P_LOGFILE
  exit 60
fi



Any Queries Mail Me vivekexpertize@gmail.com

Mtl Material Transaction Interface

                              ------Inventory Transaction Interface-------
Step 1.
CREATE  TABLE XXINV_STG1(
SUBINVENTORY_CODE VARCHAR2(10)
,TRANSACTION_DATE DATE
,TRANSACTION_QUANTITY NUMBER
,TRANSACTION_UOM VARCHAR2(3)
,PRIMARY_QUANTITY NUMBER
,SOURCE_CODE VARCHAR2(30)
,SOURCE_LINE_ID NUMBER
,source_header_id NUMBER
,process_flag NUMBER(1)
,lock_flag  NUMBER(1)
,RCV_TRANSACTION_ID NUMBER
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,CREATION_DATE DATE
,CREATED_BY NUMBER
,INVENTORY_ITEM_ID NUMBER
,ORGANIZATION_ID NUMBER
,TRANSACTION_TYPE_ID NUMBER
,TRANSACTION_ACTION_ID NUMBER
,TRANSACTION_SOURCE_TYPE_ID NUMBER
,TRANSACTION_MODE NUMBER);

Load data Into Custom Table From Flat File Using Sql Loader Or Webadi Concept

Step 2. Data File
Heading
SUBINVENTORY_CODE,TRANSACTION_DATE,TRANSACTION_QUANTITY,TRANSACTION_UOM,PRIMARY_QUANTITY,SOURCE_CODE,SOURCE_LINE_ID,source_header_id,process_flag,lock_flag,RCV_TRANSACTION_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,INVENTORY_ITEM_ID,ORGANIZATION_ID,TRANSACTION_TYPE_ID,TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_MODE
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3

Step 3. Flat File

OPTIONS(SKIP=1)
LOAD DATA
INFILE '/u01/E-BIZ/apps/apps_st/appl/inv/12.0.0/bin/XXORC_Staging_Data_File.txt'
TRUNCATE
INTO TABLE XXORC_Inv_Staging_Tab
FIELDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
(
SUBINVENTORY_CODE
,TRANSACTION_DATE  "SYSDATE"
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATED_BY
,CREATION_DATE "SYSDATE"
,CREATED_BY
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE)

Step 4. Validation

CREATE OR REPLACE procedure APPS.XXORC_Proc_Stage_Int_Valid(p_errbuff out varchar2,p_retcode out number)
as
cursor XXC is
SELECT SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag ---MANDATORY
,lock_flag ---MADATORY
,RCV_TRANSACTION_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE---MANDATORY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
from XXORC_Inv_Staging_Tab;
l_inv_id number;
l_org_id number;
l_sub_inv_code varchar2(10);
l_process_flag number;
l_uom varchar2(10);
l_transaction_type_id number;
l_error_msg varchar2(50);
l_flag varchar2(50) default 'A';
begin
for XOO in XXC
loop
---start transaction date validation---
if XOO.transaction_date>sysdate then
l_flag :='E';
l_error_msg :='Transaction Date Invalid';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
--- end of transaction date---
-- start SOURCE_LINE_ID validation---
if XOO.SOURCE_LINE_ID is null then
l_flag :='E';
l_error_msg :='Source Line Id Not Be Null';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
-- end of SOURCE_LINE_ID validation---
-- start SOURCE_LINE_ID validation---
if XOO.source_header_id is null then
l_flag :='E';
l_error_msg :='Source Header Id Not Be Null';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
-- end of SOURCE_LINE_ID validation---
----start inventory_item_id validation-----
begin
select inventory_item_id
into l_inv_id from
mtl_system_items_b
where inventory_item_id=XOO.INVENTORY_ITEM_ID;
exception
when others then
l_flag :='E';
l_error_msg :='inventory Item Id Is invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
---end of inventory_item_id validation-----
---start Unit Of Measure Validation--
begin
select UOM_CODE
into l_uom
from MTL_UNITS_OF_MEASURE_TL
where UOM_CODE=XOO.TRANSACTION_UOM;
exception
when others then
l_flag :='E';
l_error_msg :='UOM Is invalid';
fnd_file.put_line(fnd_file.log,'Error Occured '||l_error_msg);
end;
-- start organization validation----
begin
select organization_id
into l_org_id
from org_organization_definitions
where organization_id=XOO.ORGANIZATION_ID;
exception
when others then
l_flag :='E';
l_error_msg :='organization data invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
----end organization validation---
 --start sub inventory code validation---
begin
select  distinct(SUBINVENTORY_CODE)
into l_sub_inv_code
from mtl_onhand_quantities_detail
where SUBINVENTORY_CODE=XOO.SUBINVENTORY_CODE;
exception
when others then
l_flag :='E';
l_error_msg :='Sub Inventory Code Invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
---end of subinventory code validation----
--- start process flag validation---
---end of process flag validations---
--start transaction type id validation---
begin
select transaction_type_id
into l_transaction_type_id
from mtl_transaction_types
where transaction_type_id=XOO.transaction_type_id;
exception
when others then
l_flag:='E';
l_error_msg:='Transaction type Id not matched';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
-- end of transaction type id validation----
if l_flag !='E' then
insert into
mtl_transactions_interface
(TRANSACTION_HEADER_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY)
values
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
,l_sub_inv_code
,XOO.TRANSACTION_DATE
,XOO.TRANSACTION_QUANTITY
,l_uom
,XOO.PRIMARY_QUANTITY
,XOO.SOURCE_CODE
,XOO.SOURCE_LINE_ID
,XOO.source_header_id
,XOO.process_flag
,XOO.lock_flag
,XOO.RCV_TRANSACTION_ID
,l_inv_id
,l_org_id
,l_transaction_type_id
,XOO.TRANSACTION_ACTION_ID
,XOO.TRANSACTION_SOURCE_TYPE_ID
,XOO.TRANSACTION_MODE
,XOO.LAST_UPDATE_DATE
,XOO.LAST_UPDATED_BY
,XOO.CREATION_DATE
,XOO.CREATED_BY);
commit;
end if;
end loop;
end;

Step 5. Submit Standard Program

Go To Inventory Module Click Clt+L Then Type Lauch interface Manager
after Click Tool menu Click lauch manager
                        OR
Submit Standard Program Process transaction interface after Successful complete of Standard Program
another standard Program will execute Mtl Work load Program act as a temporary Table  Move From one interface To base table.

Other Method Single Insertion concept :

insert into mtl_transactions_interface
(SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE)
values
('Mobile B',
SYSDATE,
-20,
'Ea',
100,
'Inventory',
63,
26,
1,
2,
NULL,
SYSDATE,
1013497,
SYSDATE,
1013497,
222976,
204,
32,
27,
13,
3)

commit;

select * from mtl_transactions_interface

delete from mtl_transaction_interface where

select * from mtl_material_transactions where inventory_item_id=222976

delete from mtl_transactions_interface where source_code like 'Inv%'



Any Queries Mail Me vivekexpertize@gmail.com

Out-Bound Interface

                            
OUTBOUND INTERFACE:
---------------------------------

> DEVELOP THE PLSQL PACKAGE AND TRANSFER TO THE SERVER.

> OUTBOUND MEANS EXTRACT TABLE DATA TO TEXT,EXCEL,HTML ETC.

> OTHER MEANING DATABASE TABLE TO FLAT FILE.

> REGISTER OUR PROGRAM AS CONCURRENT PROGRAM.

> ATTACH TO REQUEST GROUP.

> SUBMIT THE PROGRAM.

> CHECK THE FLAT FILE.

PATH LOCATION:(AFTER THE SUBMIT THE PROGRAM OUR FILE STORE IN THIS PATH)
CHOOSE ANYONE PATH

c:\temp, c:\temp, D:\oracle\VIS\db\tech_st\11.1.0\plsql\temp, D:\oracle\VIS\db\tech_st\11.1.0\appsutil\outbound\VIS_apps, c:\temp 

OUTBOUND CODE:

CREATE OR REPLACE PROCEDURE OUTBOUND_QTY(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
IS
QTY UTL_FILE.FILE_TYPE; --DECLARE UTL LOCAL VARIABLE
PATH VARCHAR2(200);
FORMAT VARCHAR2(200);
CURSOR CAGE IS SELECT ITM.SEGMENT1,ITM.DESCRIPTION,QTY.TRANSACTION_QUANTITY,QTY.SUBINVENTORY_CODE FROM MTL_SYSTEM_ITEMS_B ITM,MTL_ONHAND_QUANTITIES QTY
WHERE ITM.ORGANIZATION_ID=QTY.ORGANIZATION_ID
AND ITM.SEGMENT1 LIKE 'SAINT%';
BEGIN
FORMAT := 'QTY_ITEMS.txt';
PATH := 'D:\oracle\VIS\db\tech_st\11.1.0\appsutil\outbound\VIS_apps';
QTY := UTL_FILE.FOPEN(PATH,FORMAT,'W'); --OPEN THE UTL FILE DATA MODE ARE  WRITE AND READ
FOR LION IN CAGE LOOP
UTL_FILE.PUT_LINE(QTY,LION.SEGMENT1||'  '|| LION.DESCRIPTION||'  '||LION.TRANSACTION_QUANTITY||'  '||LION.SUBINVENTORY_CODE);
END LOOP;
UTL_FILE.FCLOSE(QTY);  --CLOSE THE UTL FILE
END;
/

Digital Marketing Course Explanation

https://sainaatthdigitalmarketing.blogspot.in/