Journal Entry Reserve Ledger Report Query - 12.2.7 Fixed Asset Based on GTT

First run this Declaration and check the GTT Table  Data will Populate


DECLARE
  BOOK VARCHAR2(200);
  PERIOD VARCHAR2(200);
  ERRBUF VARCHAR2(200);
  RETCODE NUMBER;
  OPERATION VARCHAR2(200);
  REQUEST_ID NUMBER;
BEGIN
  BOOK := NULL;
  PERIOD := NULL;
  REQUEST_ID := NULL;

  FA_RSVLDG_REP_INS_PKG.RSVLDG(
    BOOK => 'AWM ASSET BOOK',     ---------------------parameter Name
    PERIOD => 'Jan-19',                        -------------- Period Name
    ERRBUF => ERRBUF,
    RETCODE => RETCODE,
    OPERATION => OPERATION,   
REQUEST_ID => '108336103' ---- standard concurrent request id  (Journal Entry Reserve Ledger Report)
  );
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('ERRBUF = ' || ERRBUF);
*/
  :ERRBUF := ERRBUF;
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('RETCODE = ' || RETCODE);
*/
  :RETCODE := RETCODE;
  /* Legacy output:
DBMS_OUTPUT.PUT_LINE('OPERATION = ' || OPERATION);
*/
  :OPERATION := OPERATION;
--rollback;
END;

select * from FA_RESERVE_LEDGER_GT




SELECT  --AD.DESCRIPTION  ASSET_DESCRIPTION,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE,
decode(TRANSACTION_TYPE,'B',RSV.RESERVE_ACCT, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ) GL_ACCOUNT,
RSV.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CENTER,
AD.ASSET_NUMBER ,
  AD.DESCRIPTION Asset_DESCRIPTION,
  AD.ATTRIBUTE_CATEGORY_CODE CATEGORY ,
DATE_PLACED_IN_SERVICE START_DATE,
METHOD_CODE METHOD,
RSV.LIFE LIFE,
RSV.RATE ADJ_RATE,
DS.BONUS_RATE BONUS_RATE,
RSV.CAPACITY PROD,
SUM(decode(transaction_type,'B',NULL,COST)) COST,
SUM(RSV.DEPRN_AMOUNT)     DEPRN_AMOUNT,
SUM(RSV.YTD_DEPRN) YTD_DEPRN,
SUM(RSV.DEPRN_RESERVE)          DEPRN_RESERVE,
sum(decode(transaction_type,'B',NULL,nvl(PERCENT,0))) PERCENT,
TRANSACTION_TYPE T_TYPE,
FA_FAS400_XMLP_PKG.d_lifeformula(RSV.LIFE, RSV.RATE, DS.BONUS_RATE, RSV.CAPACITY) D_LIFE
FROM
fa_reserve_ledger_gt RSV,
fa_additions AD,
gl_code_combinations CC,
&lp_fa_deprn_summary DS --- use this this table in the parameter - table name fa_deprn_summary
WHERE
RSV.ASSET_ID = AD.ASSET_ID
AND
RSV.DH_CCID = CC.CODE_COMBINATION_ID
AND
DS.PERIOD_COUNTER (+) = RSV.PERIOD_COUNTER AND
DS.BOOK_TYPE_CODE (+) = :P_Book  AND
DS.ASSET_ID (+) = RSV.ASSET_ID
GROUP BY
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
decode(transaction_type,'B', RSV.RESERVE_ACCT,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')),
RSV.DEPRN_RESERVE_ACCT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
RSV.LIFE,
  RSV.RATE,
RSV.CAPACITY,
DS.BONUS_RATE,
TRANSACTION_TYPE,
  AD.ATTRIBUTE_CATEGORY_CODE
ORDER BY
-- 1, 2, 3, 4, 5
1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 8, 12, 13, 14, 15, 16, 17 

ITEM CLASSIFICATION FOR TAX LOAD DATA

/* Formatted on 2017/02/25 15:35 (Formatter Plus v4.8.8) */
DECLARE
   rgm_item_regns_id   NUMBER;
   lv_userid           NUMBER := 44423;

   CURSOR cur
   IS
      SELECT *
        FROM xxjai_header_rgm_item_reg;

   CURSOR curr (t_item_id NUMBER)
   IS
      SELECT *
        FROM xxjai_rgm_itm_tmpl_attrs
       WHERE l_item_id = t_item_id;
BEGIN
   FOR i IN cur
   LOOP
      SELECT jai_rgm_itm_regns_s.NEXTVAL rgm_item_regns_id
        INTO rgm_item_regns_id
        FROM DUAL;

      INSERT INTO jai_rgm_itm_regns
                  (rgm_item_regns_id, regime_code, organization_id,
                   inventory_item_id, creation_date, created_by,
                   last_update_date, last_updated_by
                  )
           VALUES (rgm_item_regns_id, i.xxegime_code, i.xxorganization_id,
                   i.xx_item_id, SYSDATE, lv_userid,
                   SYSDATE, lv_userid
                  );

      FOR l IN curr (i.xx_item_id)
      LOOP
         INSERT INTO jai_rgm_itm_tmpl_attrs
                     (itm_templ_attribute_id, template_id,
                      rgm_item_regns_id, attribute_code,
                      attribute_value, creation_date, created_by,
                      last_update_date, last_updated_by
                     )
              VALUES (jai_rgm_itm_tmpl_attrs_s.NEXTVAL, NULL,
                      rgm_item_regns_id, l.attribute_code,
                      l.attribute_value, SYSDATE, lv_userid,
                      SYSDATE, lv_userid
                     );

         COMMIT;
      END LOOP;
   END LOOP;

   COMMIT;
END;

Delete Executable And Concurrent Program From Back End

declare

cursor c1 is
select fcp.CONCURRENT_PROGRAM_NAME,fa.APPLICATION_SHORT_NAME from fnd_concurrent_programs fcp,fnd_application fa
where fcp.APPLICATION_ID=fa.APPLICATION_ID
and fcp.CONCURRENT_PROGRAM_NAME='XX_PROJECT_DETAIL';

Begin
     for r1 in c1 loop
                 IF (FND_PROGRAM.PROGRAM_EXISTS
                             (program                  =>r1.CONCURRENT_PROGRAM_NAME
                             ,application              => r1.APPLICATION_SHORT_NAME ))
                         THEN
                              FND_PROGRAM.DELETE_PROGRAM(r1.CONCURRENT_PROGRAM_NAME, r1.APPLICATION_SHORT_NAME);
                                                                                   ---  Short Name of Conc Program  , ---  Application Name
                              FND_PROGRAM.DELETE_EXECUTABLE(r1.CONCURRENT_PROGRAM_NAME,r1.APPLICATION_SHORT_NAME );  
                                                                       ----  Short Name of Excutable Program  , ---  Application Name
                         commit;
                   END IF;                                        ---  Requst group Application Name
                 
 
 end loop;
end;

Create Value Set From Back End

Begin

FND_FLEX_VAL_API.CREATE_VALUESET_TABLE
(
 VALUE_SET_NAME =>'TEI_PROJECT_NO',
 DESCRIPTION =>'createdfrombackend',
 SECURITY_AVAILABLE =>'N',
 ENABLE_LONGLIST =>'N',
 FORMAT_TYPE   =>'Char',
 MAXIMUM_SIZE =>30,
 precision => NULL,
 numbers_only =>'N',
 uppercase_only  =>'N',
 right_justify_zero_fill =>'N',
 min_value  => NULL,
 MAX_VALUE   => NULL,
 TABLE_APPLICATION => 'Projects',
 table_appl_short_name =>'' ,
 TABLE_NAME =>'pa_projects_all PPA',
 ALLOW_PARENT_VALUES =>'N',
 VALUE_COLUMN_NAME =>'PPA.SEGMENT1',
 VALUE_COLUMN_TYPE  =>'Char',
 value_column_size  =>30,
 meaning_column_name  =>'PPA.NAME',
 meaning_column_type  => 'Char',
 MEANING_COLUMN_SIZE  => 30,
 ID_COLUMN_NAME    =>'PPA.PROJECT_ID',--'PRH.SEGMENT1',
 ID_COLUMN_TYPE   =>'NUMBER',--'Char',
 ID_COLUMN_SIZE   =>30,--u20,
 WHERE_ORDER_BY    =>'',
 ADDITIONAL_COLUMNS => NULL
  );
 Commit;
  Exception
  WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
  end;

SCIENTIFIC NO CONVERSION FOR IN EXCEL SHOWING (653258741+) USE THIS

<?xdofx:PR_NO||'   '?>


To display numbers completely in the Excel output of BI publisher

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CHAISE_NUMBER?></fo:b idi-override>

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?STRING_AGG_RECEIPT_NUMBER_?></fo:b idi-override>

Autonomous Transactions

CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.
CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;

We define a procedure to log error messages as an autonomous transaction.
CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

The following code forces an error, which is trapped and logged.
BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")


1 row selected.

PL/SQL Table Using Bulk Collect

/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
   TYPE amresh IS TABLE OF emp_test%ROWTYPE;

   objecttable   amresh;

   CURSOR cur
   IS
      SELECT *
        FROM emp_test;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur
      BULK COLLECT INTO objecttable;

      FORALL i IN 1 .. objecttable.COUNT
         INSERT INTO emp_test_t
              VALUES objecttable (i);
      EXIT WHEN cur%NOTFOUND;
   END LOOP;

   CLOSE cur;
END;




CREATE TABLE EMP_TEST
(
  EMP_ID     NUMBER,
  ORG_CODE   VARCHAR2(50 BYTE),
  ITEM_CODE  VARCHAR2(250 BYTE),
  E_NUMBER   VARCHAR2(100 BYTE)
)


CREATE TABLE EMP_TEST_T
(
  EMP_ID     NUMBER,
  ORG_CODE   VARCHAR2(50 BYTE),
  ITEM_CODE  VARCHAR2(250 BYTE),
  E_NUMBER   VARCHAR2(100 BYTE)
)



/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
   TYPE amresh IS TABLE OF emp_test%ROWTYPE;  -- THIS IS CALLED PL/SQL TABLE - OBJECT OF TYPE IS KNOW AS PL/SQL TABLE

   L_DATA   amresh;           ---- FROM TABLE IM GETTING DATA THROUGH AMRESH , FOR AMRESH IM DECLARING THE VARIABLE

   CURSOR cur
   IS
      SELECT *
        FROM emp_test;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur
      BULK COLLECT INTO L_DATA;

      FORALL i IN 1 .. L_DATA.COUNT
         INSERT INTO emp_test_t
              VALUES L_DATA (i);
      EXIT WHEN cur%NOTFOUND;
   END LOOP;

   CLOSE cur;
END;