UPDATE ITEM DFF (STOCK LOCATOR) VALUE USING FLAT FILE (LIKE .CSV, TXT)

STEPS

1.CREATE A STAGING TABLE

CREATE TABLE TEST_TABLE (
  organization_code VARCHAR2(10)  NOT NULL,
  item_number       VARCHAR2(80)  NOT NULL,
  stock_location    VARCHAR2(50)  NOT NULL,
  creation_date     DATE          DEFAULT sysdate NULL,
  error_message     VARCHAR2(500) NULL,
  status            VARCHAR2(20)  NULL,
  request_id        NUMBER(35,0)  NULL
)

2 WE HAVE DIFFERENT METHOD FOR UPLOADING THE DATA INTO THE STAGING TABLE  LIKE SQL LOADER,Host etc.
I WILL USE THE HOST METHOD TO UPLOAD THE DATA INTO THE STAGING TABLE FROM FLAT FILE (LIKE .CSV, TXT)
FIRST PLACE THE FLAT  FILE TO THE CUSTOM DIRECTORY I CREATED TWO FOLDER AT SERVER FIRST IS TO PLACE MY FLATE FILE (CUSTOM_TOP/process)
ONCE IT IS PROCESSED I WILL MOVE TO SOME OTHER DIRECTORY (CUSTOM_TOP/archive)  FOR BACK UP

NOW

CREATE A (.ctl) FILE

OPTIONS (SKIP=1)
LOAD DATA
APPEND INTO TABLE  "TEST_TABLE"
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 
(
ORGANIZATION_CODE ,
ITEM_NUMBER       ,
STOCK_LOCATION TERMINATED BY WHITESPACE,
CREATION_DATE   sysdate   ,
ERROR_MESSAGE     ,
STATUS constant 'U',
REQUEST_ID
)
-- SAVE YOUR FILE (TEST.ctl) AND PLACE FILE  TO THE CUSTOM_TOP/bin FOLDER ON SERVER

CREATE THE HOST FILE (DON'T GIVE ANY EXTENSTION ) WHICH WILL PULL DATA FROM FLAT FILE AND INSERT INTO  THE STAGING TABLE USING(.CTL) FILE

SOURCE_FILE=`echo $1 | awk '{print $9}'| sed -e "s/\"//g"`              #Source File
SOURCE_DIR="CUSTOM_TOP/process"
DEST_DIR="CUSTOM_TOP/archive"
apps="USER/PWD"
echo "#################################################################################"
DATAFILE=$CUSTOM_TOP/process/$SOURCE_FILE  #
BACKUPFILE=$CUSTOM_TOP/archive/`date +$SOURCE_FILE-%Y%m%d%H%M%S`
CTLFILE=$CUSTOM_TOP/bin/CTL FILE NAME    #TEST.ctl
LOGFILE=$CUSTOM_TOP/log/$SOURCE_FILE.log
BADFILE=$CUSTOM_TOP/log/$SOURCE_FILE.bad
DISFILE=$CUSTOM_TOP/log/$SOURCE_FILE.dis
echo "#################################################################################"
##echo "Parameter9 :"$SOURCE_FILE
##echo "DATAFILE :"$DATAFILE
##echo "BACKUPFILE :"$BACKUPFILE
##echo "CTLFILE :"$CTLFILE
echo "#################################################################################"
sqlload silent=header silent=feedback userid=$USER control=$CTLFILE  data=$DATAFILE  bad=$BADFILE  log=$LOGFILE  discard=$DISFILE
mv "$CUSTOM_TOP/process/$SOURCE_FILE" "$CUSTOM_TOP/archive/`date +$SOURCE_FILE-%Y%m%d%H%M%S`"

--SAVE YOUR FILE (TEST_HOST) AND PLACE FILE  TO THE CUSTOM_TOP/bin FOLDER ON SERVER

Register a program(data_upload) select execution method as host and check  your data is loading into the staging table form flat file or not!!


NOW CREATE THE PROCEDURE TO VAIDATE THE DATA AND THEN UPDATE THE DFF FIELD

CREATE OR REPLACE PROCEDURE UPDATE_DFF(errbuf  OUT      VARCHAR2
                                       ,retcode    OUT      NUMBER
                                       ,p_req_id IN NUMBER)
AS



CURSOR CUR_UPDATE_DFF
IS
SELECT a.ORGANIZATION_CODE
,a.ITEM_NUMBER
,a.STOCK_LOCATION
,(SELECT ORGANIZATION_ID
 FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE=a.ORGANIZATION_CODE) ORGANIZATION_ID
,a.STATUS
,a.REQUEST_ID
,a.ERROR_MESSAGE
FROM TEST_TABLE a;

v_ErrorText VARCHAR2(200):=SQLERRM;
v_error_code NUMBER:=SQLCODE ;
vattribute1  MTL_SYSTEM_ITEMS_B.attribute1%TYPE  ;

p_update EXCEPTION;   --define exception

v_ORGANIZATION_ID NUMBER(10);
V_ITEM_NUMBER VARCHAR2(40);
v_ORGANIZATION_code NUMBER(10);
v_error_status VARCHAR2(40);

v_request_id2 NUMBER:=p_req_id;

rec CUR_UPDATE_DFF%ROWTYPE;

BEGIN -- print the data into the output file

  fnd_file.put_line (fnd_file.output,'            TEST TO CHECK Update Item DFF (Stock Locator)               ');
  fnd_file.put_line (fnd_file.output,'');
  fnd_file.put_line (fnd_file.output,'Run Date          : '|| TO_CHAR(SYSDATE,'DD-MON-RRRR HH:MI:SS'));
  fnd_file.put_line (fnd_file.output,'');
  fnd_file.put_line (fnd_file.output,'-----------------------------------------------------------------------------------------------------------------------------------------------------------------');
  fnd_file.put_line (fnd_file.output,'ORG_CODE'||'        '||'STOCK_LOCATION'||'                 '||'ITEM_NUMBER                   '||'                 '||'ERROR_MESSAGE');
  fnd_file.put_line (fnd_file.output,'-----------------------------------------------------------------------------------------------------------------------------------------------------------------');

  for rec in CUR_UPDATE_DFF
LOOP
  IF v_request_id2= rec.REQUEST_ID THEN --check the request id 
    BEGIN
                BEGIN
                     SELECT ORGANIZATION_CODE INTO v_ORGANIZATION_CODE
                     FROM   ORG_ORGANIZATION_DEFINITIONS
                     WHERE ORGANIZATION_CODE=rec.ORGANIZATION_CODE;
                     EXCEPTION
                       WHEN No_Data_Found THEN
                        fnd_file.put_line (fnd_file.LOG,rec.ORGANIZATION_CODE||' - '||'Organization Code does not exist');
                END;

                BEGIN
                     SELECT SEGMENT1|| '-' ||SEGMENT2|| '-' ||SEGMENT3,organization_id ,
                    attribute1 INTO V_ITEM_NUMBER,v_ORGANIZATION_ID,vattribute1
                     FROM MTL_SYSTEM_ITEMS_B
                     WHERE  organization_id=rec.ORGANIZATION_ID
                     AND SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3 =rec.ITEM_NUMBER;
                     EXCEPTION
                       WHEN No_Data_Found THEN
                      fnd_file.put_line (fnd_file.LOG,rec.ITEM_NUMBER ||' - ' ||'Item Number combination does not exist');
                END;

      IF (v_organization_id =rec.organization_id AND  V_ITEM_NUMBER=rec.ITEM_NUMBER )

     THEN
     IF  vattribute1=rec.STOCK_LOCATION
     THEN
       fnd_file.put_line (fnd_file.Log,'Duplicate record found!!!!'||rec.STOCK_LOCATION||'-'||'for the Item -'||rec.ITEM_NUMBER);
       END IF ;

     BEGIN
      UPDATE mtl_system_items_b
        SET attribute1=rec.STOCK_LOCATION
          WHERE organization_id=v_ORGANIZATION_ID
             AND SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3 =rec.ITEM_NUMBER;

              COMMIT;


      END;

      BEGIN

       UPDATE TEST_TABLE
      SET status='P' ,    --Process record
      ERROR_MESSAGE='Completed Succesfully'
      WHERE REQUEST_ID = v_request_id2
      AND ORGANIZATION_CODE=rec.ORGANIZATION_CODE AND ITEM_NUMBER=rec.ITEM_NUMBER;
      COMMIT;
      END;

      ELSE

        RAISE p_update;

     END IF;
     fnd_file.put_line (fnd_file.output,rec.ORGANIZATION_CODE||'             '||rec.STOCK_LOCATION||'                    '||rec.ITEM_NUMBER               ||'                    '||'Completed Succesfully');
     EXCEPTION
     WHEN p_update THEN
 UPDATE TEST_TABLE
       SET ERROR_MESSAGE='Organization Code OR Item Number combination does not exist',status='E'  --Exception
       WHERE ORGANIZATION_CODE NOT IN (SELECT ORGANIZATION_CODE  FROM   ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE=rec.ORGANIZATION_CODE )
       AND ITEM_NUMBER NOT IN (SELECT SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3  FROM mtl_system_items_b)
       AND REQUEST_ID = v_request_id2 ;
       COMMIT;
       fnd_file.put_line (fnd_file.output,rec.ORGANIZATION_CODE||'             '||rec.STOCK_LOCATION||'                    '||rec.ITEM_NUMBER               ||'                    '||'Organization Code OR Item Number combination does not exist');
    END;
 END IF;

 BEGIN
       UPDATE TEST_TABLE
       SET ERROR_MESSAGE='Organization Code OR Item Number combination does not exist',status='E'
       WHERE  REQUEST_ID = v_request_id2
       AND status='U';
       COMMIT  ;
 END;

end loop;

END UPDATE_DFF;

REGISTER A CONCURRENT PROGRAM (UPDATE_DFF)  FOR THE UPPER PROCEDURE AND CHECK WHETHER ITS UPDATING DFF FIELD OR NOT

NOW I CREATE ONE PACKAGE WHICH WILL SUBMIT BOTH THE PROGRAM SEQUENTIALLY
1.FOR INSERTING DATA INTO STAGING TABLE
 2.UPDATE DFF VALUE

--PACKAGE SPECIFICATION
CREATE OR REPLACE package UPDATE_DFF_PKG
 as
 procedure UPLOADATA_UPDATE_DFF(errbuf       OUT      VARCHAR2
                                ,retcode      OUT      NUMBER
                               ,p_file in VARCHAR2);


 END UPDATE_DFF_PKG;

 -- PACKAGE BODY 

 CREATE OR REPLACE package body UPDATE_DFF_PKG
  as
procedure UPLOADATA_UPDATE_DFF(errbuf       OUT      VARCHAR2
                               ,retcode      OUT      NUMBER
                              ,p_file in varchar2)
 AS

     v_request_id    NUMBER :=0;
       v_request_id1   NUMBER :=0;
     v_record_id     NUMBER;
     v_req_status   BOOLEAN;
     v_phase        VARCHAR2 (200);
     v_status       VARCHAR2 (200);
     v_dev_phase    VARCHAR2 (200);
     v_dev_status   VARCHAR2 (200);
     v_message      VARCHAR2 (500);
     v_file VARCHAR2(30):=p_file;

BEGIN

     BEGIN                                                       
        v_request_id := fnd_request.submit_request ('CUSTOM'        -- APPPLICATION
                                                ,'data_upload'   -- PROGRAM
                                                ,NULL                       -- DESCRIPTION
                                                ,NULL                       -- START TIME
                                                ,FALSE
                                                ,v_file                      -- File Name(Source File)
                                                  );
        COMMIT;
        fnd_file.put_line (fnd_file.LOG, 'loading data into staging table successfully');
        -- +--------+
        IF v_request_id > 0
           THEN
        -- +--------+
            BEGIN
              LOOP
          -- +--------+
              v_req_status :=
                 fnd_concurrent.get_request_status

                                                 (request_id          => v_request_id,
                                                  appl_shortname      => 'data_upload',
                                                  program             => 'data_upload',
                                                  phase               => v_phase,
                                                  status              => v_status,
                                                  dev_phase           => v_dev_phase,
                                                  dev_status          => v_dev_status,
                                                  MESSAGE             => v_message
                                                 );

              IF UPPER (v_dev_phase) = 'COMPLETE'
              THEN
              EXIT;
              END IF;
              END LOOP;
           -- +--------+
                 EXCEPTION
           WHEN OTHERS
           THEN
              fnd_file.put_line
                 (fnd_file.LOG,
                     'Error while checking "loading data into  staging table " status '
                  || SQLERRM
                 );
            END;
        -- +--------+
        END IF;
        -- +--------+

          fnd_file.put_line (fnd_file.LOG,
                           '"loading data into staging table" status : '
                        || v_dev_phase
                        || ' '
                        || v_dev_status
                       );
      COMMIT;

     EXCEPTION
     WHEN OTHERS
     THEN
        fnd_file.put_line (fnd_file.LOG,'Unexpected Error occured while running "loading data into staging table" *****  '|| ' - '|| SQLERRM);
    END;

    BEGIN
    UPDATE TEST_TABLE
    SET REQUEST_ID=v_request_id
    WHERE REQUEST_ID IS NULL;
      COMMIT;
    END;


    BEGIN
      v_request_id1 := FND_REQUEST.SUBMIT_REQUEST('CUSTOM'        -- APPPLICATION
                                                ,'UPDATE_DFF'   -- PROGRAM
                                                ,NULL                       -- DESCRIPTION
                                                ,NULL                       -- START TIME
                                                ,FALSE
                                                ,v_request_id
                                                );
      COMMIT;
    --  fnd_file.put_line (fnd_file.LOG, 'UPDATE DATA SUCESSFULLY');
      -- +--------+
      IF v_request_id1 > 0
         THEN
        -- +--------+
        BEGIN
         LOOP
          -- +--------+
              v_req_status :=
                 fnd_concurrent.get_request_status

                                                 (request_id          => v_request_id1,
                                                  appl_shortname      => 'CUSTOM',
                                                  program             => 'UPDATE_DFF',
                                                  phase               => v_phase,
                                                  status              => v_status,
                                                  dev_phase           => v_dev_phase,
                                                  dev_status          => v_dev_status,
                                                  MESSAGE             => v_message
                                                 );

              IF UPPER (v_dev_phase) = 'COMPLETE'
              THEN
              EXIT;
              END IF;
          END LOOP;
          -- +--------+
                 EXCEPTION
           WHEN OTHERS
           THEN
              fnd_file.put_line
                 (fnd_file.LOG,
                     'Error while checking " Updating data in the table " status '
                  || SQLERRM
                 );
        END;
      -- +--------+
      END IF;
      -- +--------+
          fnd_file.put_line (fnd_file.LOG,
                           '"updating data into base table" status : '
                        || v_dev_phase
                        || ' '
                        || v_dev_status
                       );
      COMMIT;

     EXCEPTION
     WHEN OTHERS
     THEN
        fnd_file.put_line (fnd_file.LOG,'Unexpected Error occured while running " Updating the data in the table" *****  '|| ' - '|| SQLERRM);
    END;
END  UPLOADATA_UPDATE_DFF;
END UPDATE_DFF_PKG;

Register the program for the package and run program from SRS windw and check

Get Transaction detail for a Account which have different sources like (Inventory,Purchasing,Manual,Spreadsheet,,Recurring,Payables,Receivables) in general ledgers

CREATE OR REPLACE VIEW ACCOUNT_INQUIRY_V (

  transaction_date,

  po,

  item_code,

  item_description,

  item_category,

  supplier,

  qty,

  uom,

  unit_price,

  charge_account,

  rt_no,

  source,

  journal_entry,

  period_name,

  line_number

) AS

----------------- INVENTORY -----------------------------------

SELECT TO_CHAR(mmt.transaction_date,'dd-mon-yyyy')                                            Transaction_Date

     , pha.segment1                                                                           PO

     , To_char(msib.segment1)||'-'||To_char(msib.segment2)||'-'||To_char(msib.segment3)                                  Item_Code

     , msib.description                                                                       Item_Description

     , Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

     , pv.vendor_name                                                                         Supplier

     , mmt.primary_quantity                                                                   QTY

     , mmt.transaction_uom                                                                    UOM

     , gjl.accounted_dr                                                                       Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , rsh.receipt_num                                       RT_No

     , gjh.je_source                                         Source

     , gjh.name                                              Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')                     Period_Name

     , gjl.je_line_num                                        line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , mtl_material_transactions mmt

     , mtl_system_items_b msib

     , gl_code_combinations gcc

     , rcv_transactions rt

     , rcv_shipment_headers rsh

     , po_headers_all pha

     , po_vendors pv

     , rcv_shipment_lines rsl

     , mtl_categories_b  mcb

WHERE  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.period_name         = gps.period_name

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjb.je_batch_id         = gjh.je_batch_id

  AND  mmt.transaction_id      = TO_NUMBER(TRIM(gjl.reference_3))

  AND  mmt.inventory_item_id   = msib.inventory_item_id

  AND  mmt.organization_id     = msib.organization_id

  AND  mmt.rcv_transaction_id  = rt.transaction_id(+)

  AND  rt.shipment_line_id     = rsl.shipment_line_id (+)

  AND  rsl.CATEGORY_ID         = mcb.CATEGORY_ID(+)

  AND  rt.shipment_header_id   = rsh.shipment_header_id (+)

  AND  rt.po_header_id         = pha.po_header_id (+)

  AND  pha.vendor_id           = pv.vendor_id (+)

  AND gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name                  = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Inventory'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- PURCHASING-----------------------------------

SELECT TO_CHAR(rt.transaction_date ,'DD-MON-YY') Transaction_Date

     , pha.segment1                              PO

     , To_Char(rsl.ITEM_ID)                      Item_Code

     , rsl.item_description                      Item_Description

     , Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

     , pv.vendor_name                            Supplier

     , rt.quantity                               Qty

     , rt.unit_of_measure                        UOM

     , gjl.accounted_dr                          Unit_Price

     , gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'|| gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7  Charge_Account

     , rsh.receipt_num                            RT_No

     , gjh.je_source                              Source

     , gjh.name                                   Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

     , po_headers_all pha

     , rcv_transactions rt

     , po_vendors pv

     , rcv_shipment_headers rsh

     , rcv_shipment_lines rsl

     ,mtl_categories_b  mcb

WHERE  gps.set_of_books_id              = gjl.set_of_books_id

  AND  gjb.je_batch_id                  = gjh.je_batch_id

  AND  gjh.je_header_id                 = gjl.je_header_id

  AND  gjl.period_name                  = gps.period_name

  AND  gjl.code_combination_id          = gcc.code_combination_id

  AND  TO_NUMBER(TRIM(gjl.reference_2)) = pha.po_header_id

  AND  TO_NUMBER(TRIM(gjl.reference_5)) = rt.transaction_id

  AND  rt.shipment_header_id            = rsh.shipment_header_id

  AND  pha.vendor_id                    = pv.vendor_id

  AND  rt.shipment_line_id              = rsl.shipment_line_id

  AND  rsl.CATEGORY_ID                   = mcb.CATEGORY_ID

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name                  = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Purchasing'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- MANUAL -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

 AND  gps.period_name NOT LIKE '&PERIOD'  /*period should be in format MON-YY*/

  AND  gps.application_id      = '&APPLICATION_ID'

--  AND  gjl.code_combination_id ='&CODE_COMBINATION_ID' /OPTIONAL/

  AND  gjh.je_source           = 'Manual'

--  AND  gjl.je_LINE_NUM= &LINE_NUM  /* OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE */

UNION

----------------- SPREADSHEET -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = &APPLICATION_ID

--  AND  gjl.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--  AND  gps.period_name         = '&PERIOD_NAME'

  AND  gjh.je_source           = 'Spreadsheet'

UNION

----------------- RECURRING -----------------------------------

SELECT NULL                 Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

     , NULL                 RT_No

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     ,gjl.je_line_num                            line_number

FROM   gl_period_statuses gps

     , gl_je_lines gjl

     , gl_je_headers gjh

     , gl_je_batches gjb

     , gl_code_combinations gcc

WHERE  gjb.je_batch_id         = gjh.je_batch_id

  AND  gjh.je_header_id        = gjl.je_header_id

  AND  gjl.period_name         = gps.period_name

  AND  gps.set_of_books_id     = gjl.set_of_books_id

  AND  gjl.code_combination_id = gcc.code_combination_id

  AND  gps.period_name NOT LIKE '%ADJ%'

  AND  gps.application_id      = &APPLICATION_ID

--  AND  gjl.code_combination_id = '&CODE_COMBINATION_ID' /* OPTIONAL */

--  AND  gps.period_name         = '&PERIOD_NAME' /*period should be in (MON-YY)format */

  AND  gjh.je_source           = 'Recurring'

--  AND  gjl.je_LINE_NUM=&LINE_NUM  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/

UNION

----------------- PAYABLES -----------------------------------

SELECT  TO_CHAR(rt.transaction_date ,'DD-MON-YY') Transaction_Date

, pha.segment1 PO

, To_Char(rsl.ITEM_ID) Item_Code

, rsl.item_description Item_Description

, Decode(mcb.SEGMENT1,mcb.segment2,mcb.segment3,NULL,'NULL', mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3) Item_Category

, pv.vendor_name Supplier

, rt.quantity Qty

, rt.unit_of_measure UOM

, rt.po_unit_price Unit_Price

, gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'|| gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 Charge_Account

, rsh.receipt_num

, gjh.je_source Source

, gjh.name Journal_Entry

, to_date(gps.PERIOD_NAME,'MON-YY') Period_Name

, gjl.je_line_num line_number

FROM gl_period_statuses GPS

, gl_je_lines GJL

, gl_je_headers GJH

, gl_je_batches GJB

, gl_code_combinations GCC

, ap_invoices_all AIA

, AP_INVOICE_DISTRIBUTIONS_ALL AIDA

, PO_VENDORS PV

, PO_DISTRIBUTIONS_ALL PDA

, PO_HEADERS_ALL PHA

, RCV_TRANSACTIONS RT

, RCV_SHIPMENT_LINES RSL

, RCV_SHIPMENT_HEADERS RSH

, mtl_categories_b mcb

WHERE gjb.je_batch_id = gjh.je_batch_id

AND gjh.je_header_id = gjl.je_header_id

AND gjl.period_name = gps.period_name

AND gps.set_of_books_id = gjl.set_of_books_id

AND gjl.code_combination_id = gcc.code_combination_id

--AND aia.invoice_id=aida.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_2))= aia.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_2))= aida.invoice_id

AND TO_NUMBER(TRIM(gjl.reference_3))= aida.distribution_line_number

AND aia.vendor_id = pv.vendor_id

AND aida.po_distribution_id = pda.po_distribution_id

AND aida.RCV_TRANSACTION_ID = rt.TRANSACTION_ID

AND pha.po_header_id=rt.po_header_id

AND rt.shipment_line_id = rsl.shipment_line_id

AND rsl.category_id = mcb.category_id

AND rt.shipment_header_id = rsh.shipment_header_id

AND gps.application_id ='&APPLICATION_ID'

--AND GJL.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--AND GPS.period_name = '&PERIOD_NAME' /*period should be in (MON-YY)format */

AND GJH.je_source = 'Payables'

UNION

----------------- RECEIVABLES -----------------------------------

SELECT (SELECT TO_CHAR(max(rcta.trx_date) ,'DD-MON-YY') 

          FROM ra_customer_trx_all rcta

         WHERE rcta.trx_number = GJL.reference_4) Transaction_Date

     , NULL                 PO

     , NULL                 Item_Code

     , NULL                 Item_Description

     , NULL                 Item_Category

     , NULL                 Supplier

     , NULL                 Qty

     , NULL                 UOM

     , NULL                 Unit_Price

     , gcc.segment1||'.' ||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7   Charge_Account

    -- , rcta.trx_number

     , GJL.reference_4 trx_number

     , gjh.je_source        Source

     , gjh.name             Journal_Entry

     , to_date(gps.PERIOD_NAME,'MON-YY')          Period_Name

     , gjl.je_line_num                            line_number

FROM gl_period_statuses GPS

    ,gl_je_lines GJL

    ,gl_je_headers GJH

    ,gl_je_batches GJB

    ,gl_code_combinations GCC

  --  ,ra_customer_trx_all rcta

WHERE  GJB.je_batch_id      = GJH.je_batch_id

AND GJH.je_header_id        = GJL.je_header_id

AND gps.set_of_books_id     = gjl.set_of_books_id

AND GJL.period_name         = GPS.period_name

AND GJL.code_combination_id = GCC.code_combination_id

--AND TO_NUMBER(TRIM(GJL.reference_2)) = rcta.CUSTOMER_TRX_ID(+)

AND gps.period_name NOT LIKE '%ADJ%'

AND gps.application_id      = '&APPLICATION_ID'

AND GJH.je_source           = 'Receivables'

AND LENGTH(TRIM(TRANSLATE (GJL.reference_2, '0123456789',' '))) IS NULL

--AND GJL.code_combination_id = '&CODE_COMBINATION_ID' /OPTIONAL/

--AND GPS.period_name         = '&PERIOD_NAME' /*period should be in (MON-YY)format */

--AND gjl.je_line_num  = '&LINE_NUM'  /*OPTIONAL IF YOU WANT DETAIL FOR PARTICULAR LINE*/;

Query to test the View

SELECT * FRoM ACCOUNT_INQUIRY_V WHERE CHARGE_ACCOUNT='&ACCOUNT_NUMBER'

AND period_name LIKE '&PERIOD_NAME' /*period should be in (MON-YY)format */

AND source = '&SOURCE' 

Register Tables and Columns in ORACL APPS

Create table taking required columns USING CREATE TABLE STATEMENT

1.Pass the following paramter to the  below procedure for register a new table

 Procedure REGISTER_TABLE( p_appl_short_name in varchar2,
    p_tab_name in varchar2,
    p_tab_type in varchar2,
    p_next_extent in number default 512,
    p_pct_free in number default 10,
    p_pct_used in number default 70);
  
  
    EXAMPLE
  
    BEGIN
AD_DD.REGISTER_TABLE ('FND','TABLE_NAME','T');
END;

2.Pass the following paramter to the  below procedure for register columns of a table

Procedure REGISTER_COLUMN(p_appl_short_name in varchar2,
    p_tab_name in varchar2,
    p_col_name in varchar2,
    p_col_seq in number,
    p_col_type in varchar2,
    p_col_width in number,
    p_nullable in varchar2,
    p_translate in varchar2,
    p_precision in number default null,
    p_scale in number default null);
  
EXAMPLE
  
BEGIN
AD_DD.REGISTER_COLUMN ('FND', 'TABLE_NAME','COLUMN_NAME', 1, 'VARCHAR2', 150, 'Y', 'N');
END;
  
  
Finally check in below query,whether it is registerd or not!!!


SELECT * FROM FND_TABLES  
WHERE TABLE_NAME LIKE'TABLE_NAME'

Get the Application url for the current session

Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
       (SELECT PROFILE_OPTION_ID
        FROM FND_PROFILE_OPTIONS
        WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
        AND LEVEL_VALUE=0;

FND_PROFILE to use the current value for the session

FND_PROFILE.VALUE :
This is a function which returns a character string. It can be used to retrieve the current value of the specified user profile option.
Example:
fnd_profile.value(‘PROFILEOPTION’) 

fnd_profile.value(’MFG_ORGANIZATION_ID’)

fnd_profile.value(’LOGIN_ID’)

fnd_profile.value(’USER_ID’)

fnd_profile.value(’USERNAME’)

fnd_profile.value(’CONCURRENT_REQUEST_ID’)

fnd_profile.value(’GL_SET_OF_BKS_ID’)

fnd_profile.value(’ORG_ID’)

fnd_profile.value(’SO_ORGANIZATION_ID’)

fnd_profile.value(’APPL_SHRT_NAME’)

fnd_profile.value(’RESP_NAME’)

fnd_profile.value(’RESP_ID’)

Find Responsibility name and Request group name attached to a concurrent program

Select Frvl.Responsibility_Name,
fe.executable_name,
frg.request_group_name,
User_Concurrent_Program_Name
From Apps.Fnd_Concurrent_Programs_Tl fcpt,
APPS.FND_REQUEST_GROUP_UNITS frgu,
Apps.Fnd_Request_Groups Frg,
Apps.Fnd_Responsibility_Vl frvl,
Apps.Fnd_Application_Vl fav,
Apps.Fnd_Executables fe,
Apps.Fnd_Concurrent_Programs fcp
Where frvl.Request_Group_Id=frgu.Request_Group_Id
And frgu.Request_Unit_Id = fcpt.Concurrent_Program_Id
and executable_name like '&EXECUTABLE_NAME'  /*optional*/
and fcp.executable_id = fe.executable_id
And User_Concurrent_Program_Name Like '&PROGRAM_NAME'
And fcp.Concurrent_Program_Id = fcpt.Concurrent_Program_Id
And Frg.Request_Group_Id = Frgu.Request_Group_Id
and frvl.request_group_id = frg.request_group_id
and frg.application_id = fav.application_id

Create and Register of a Custom Form in Oracle Applications(11i and R12)

Steps to

Create a new form in oracle Apps R12

 1. Check the Form builder version is compatible with your application server or not.

2. Download all the necessary .FMb and .PLL files from the server

  

FMB- TEMPLATE.fmb and APPSTAND.fmb files

Server location FMB -$AU_TOP/forms/US

Server location PLL - $AU_TOP/resource (Try to download all PLL files)

First try to download below PLL files see if it works .If it does work then you don't need to download all the PLL files

APPCORE.pll                   APPCORE2.pll              APPDAYPK.pll

APPFLDR.pll                   CUSTOM.pll                   FNDSQF.pll

FV.pll                               GHR.pll                           GLOBE.pll

GMS.pll                           HRKPI.pll                       IGILUTIL.pll

IGILUTIL2.pll                 JA.pll                               JE.pll

JL.pll                              OPM.pll                            PSA.pll

PQH_GEN.pll               PSB.pll                            PSAC.pll

VERT.pll                      VERT1.pll                    VERT2.pll

VERT3.pll                     VERT4.pll                VERT5.pll

3. Set Forms60_Path in Registry

Temporary folder and place all the PLL and FMB files.

Now go to

Start->Run->Regedit->HKEY_LOCAL_MACHINE->Software->Oracle

Here you can find the oracle home.  In this home check for FORMS60_PATH; append the path of your folder

 Where all the libraries and the form is present, prefixed with a semicolon (;)

                         OR

                                                                                                 

You can direct set the path using Forms Builder

Right Click on Forms Builder->properties->send menu (shortcut) ->Start in :-> you can put the path of folder where you place your all PLL and FMB files.

                                                 

4. Always create a new form with TEMPLATE.fmb file.

Open the Forms Builder tool and connect it

Now open the TEMPLATE.fmb form from your local working directory

Remove Block “DETAILBLOCK”, Canvas "BLOCKNAME" Remove Window "BLOCKNAME" and

 Create a new window, rename (Ex: TEST_WIN) and set property Function->primary canvas property to the newly created canvas.

 Create a new canvas, rename (Ex: TEST_CAN) set property Physical->window property to the newly created window.

 Create your blocks, items, triggers and relationships as per requirement.

  Save your new FORM as with a different filename. Also rename the module with the same name as the filename.

 

  Set the Form Module property

 

  Function->Console window->Point to the newly created window.

 

  First Navigation data block->Point to your Data Block.

  Save your FORM frequently!!

 

 

5. Apply property classes to each item to ensure the same look like as the rest of Oracle Apps forms.

For each Module, Canvas, Window etc., apply the Property Classes that is most appropriate.

6. Changes need to be made in the Triggers and Program Units

Change the code in Pre-Form Trigger

app_window.set_window_position ('TEST_WIN', 'FIRST_WINDOW'); /*Data Block name */

Change the code in APP_CUSTOM Package Body

If (wnd = ‘<first window>’) then   /* Window Name */

app_window.close_first_window;

SAVE YOUR WORK.

7. Open the FTP tool (like putty, filezilla, talnet, or connect the server using the CMD window)

Login to the tool and

Always move your form (.FMB) to the $AU_TOP/forms/US

Now compile the form using the below command

For Oracle Applications 11i

f60gen module=$AU_TOP/forms/US/FORM_NAME.fmb userid=apps/apps output_file==$CUSTOM_TOP/forms/US/FORM_NAME.fmx

 module_type=form batch=yes compile_all=special

instead of CUSTOM_TOP you can use any of your application_top for output(.FMX) file.

For Oracle Applications R12

 frmcmp_batch module=$AU_TOP/forms/US/FORM_NAME.fmb

userid=APPS/***** output_file=$CUSTOM_TOP/forms/US/FORM_NAME.fmx

module_type=form batch=yes compile_all=special

/*Use the same application_top at form registration time.

Press enter to execute the command, Now that Command creating (or replacing) the .fmx file into

Represented path.

8. Register the Custom Form into Oracle Application-

Go to the Application Developer Responsibility

If this responsibility is not there, then assign this responsibility to your username

Now go to Application ->Form

Register your Custom form here

Fields

Form: Enter the file name of your form (the name of the .fmx file) without extension.

Your form filename must be all uppercase, and its output file (.fmx) file must be located in your

application top.

Application : Enter the name of the application which will own this form.

User Form Name : This is the form_name you see when selecting a form using the

Functions window.

Description : Enter a suitable description for your form.

Register Form Functions

Navigation – Application Developer -> Application->Function

Give the Function name and user function name

Click on the form tab and you will see following screen

Fields

Function : Enter a unique function name for your function. This function name can be

Used while calling this program programmatically. This name is not visible to the user

Through other forms.

Form: Select the form name which you have registered.

Application: Select the application name for your form.

Parameters: Enter the parameters that you want to pass to your form function. E.g.

Query only.

Creating Menu of Functions

Navigation – Application Developer -> Application->Menu

Fields

Menu: Create new menu or if you want to attached your form for existing menu Query the menu _name. This name is not visible to the user.

User Menu Name: The user menu name is used when a responsibility calls a menu.

Menu Type: The options in menu type include:

• Standard – for menus that would be used in the Navigator form

• Tab – for menus used in self-service applications tabs

• Security – for menus that are used to aggregate functions for data security or

Specific function security purposes, but would not be used in the Navigator form

Seq : Enter a sequence number.

Prompt : Enter the prompt that the users will see for the menu.

Submenu: If you want another menu to be called from this menu, then enter this menu

Name in this field.

Function: Enter the form function name in this field.

Description: Enter a suitable description for the menu.

Grant: The Grant check box should usually be checked. Checking this box indicates that

This function is automatically enabled for the user. If this is not checked then the function

Must be enabled using additional data security rules.

One you will save the form you will get a note like

View Tree: Click on View Tree Button and you will see following screen with the full

Hierarchy of the menu.

Now go the Responsibility where your menu is attached and use your custom form!!!!