Date format in reports

In reports Parameter use the below format

TRUNC(prh.creation_date)>=nvl(FND_DATE.CANONICAL_TO_DATE(:p_from_date),TRUNC(prh.creation_date))
AND TRUNC(prh.creation_date)<=nvl(FND_DATE.CANONICAL_TO_DATE(:p_to_date),TRUNC(prh.creation_date))

For the above format use the FND_DATE4_REQUIRED_STANDARD valueset.

Query for flex_value valueset

SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');

Update expense account for the item in mtl_system_items_interface

Register as concurrent program to get the result. Trying from Back end is not advisable.
===============================================================

For the below custom table having itemcode(
mtl_system_items_interface.segment1 and new expense account)
CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE
(
  ITEMCODE              VARCHAR2(240 BYTE),
  EXPENSE_ACCOUNT_CODE  VARCHAR2(240 BYTE),
  STATUS                VARCHAR2(240 BYTE),
  ERROR_MESSAGE         VARCHAR2(240 BYTE)
)

CREATE TABLE APPS.XXAYTB_GLCODE_CCID_CHECK
(
  SEGMENT1         VARCHAR2(240 BYTE),
  SEGMENT2         VARCHAR2(240 BYTE),
  SEGMENT3         VARCHAR2(240 BYTE),
  SEGMENT4         VARCHAR2(240 BYTE),
  SEGMENT5         VARCHAR2(240 BYTE),
  SEGMENT6         VARCHAR2(240 BYTE),
  NEWCCID          VARCHAR2(240 BYTE),
  OLDCCID          VARCHAR2(240 BYTE),
  ITEMCODE         VARCHAR2(240 BYTE),
  ORGID            VARCHAR2(240 BYTE),
  EXPENSE_ACCOUNT  VARCHAR2(240 BYTE)
)

CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE_STATUS
(
  INVENTORY_ITEM_ID    VARCHAR2(240 BYTE),
  ORGANIZATION_ID      VARCHAR2(240 BYTE),
  ITEMCODE             VARCHAR2(240 BYTE),
  MSIB_CURRVAL         VARCHAR2(240 BYTE),
  CODE_COMBINATION_ID  VARCHAR2(240 BYTE),
  STATUS               VARCHAR2(240 BYTE)
)

/* Formatted on 2013/02/24 09:36 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.xxinv_item_assignment_v1 (
   errbuff   OUT   VARCHAR2,
   retcode   OUT   NUMBER
)
---procedure created for updating expense account for an item  senthil kalvarayapillai wipro 20-feb-2013
IS
   v_error_msg             VARCHAR2 (2000);
   v_msg_cnt               NUMBER;
   v_category_cnt          NUMBER;
   v_set_process_id        NUMBER;
   v_code_combination_id   NUMBER;
   l_segment1              VARCHAR2 (100);
   l_segment2              VARCHAR2 (100);
   l_segment3              VARCHAR2 (100);
   l_segment4              VARCHAR2 (100);
   l_segment5              VARCHAR2 (100);
   l_segment6              VARCHAR2 (100);
   l_item                  VARCHAR2 (100);
   l_ccid                  VARCHAR2 (100);
   l_organization_id       VARCHAR2 (100);
   l_inventory_item_id     VARCHAR2 (100);
   v_charge_ccid_id        NUMBER;
BEGIN
   fnd_file.put_line
      (fnd_file.output,
       '============================INSERTED Records==============================='
      );
   FOR rec_c1 IN (SELECT *
                    FROM xxaytb_glcode_update)
   LOOP
      DBMS_OUTPUT.put_line (rec_c1.itemcode);
      FOR rec_c2 IN (SELECT glcc.segment1, glcc.segment2, glcc.segment3,
                            glcc.segment4, glcc.segment5, glcc.segment6,
                            msib.segment1 item,
                            glcc.code_combination_id oldccid,
                            msib.organization_id, msib.inventory_item_id,
                            rec_c1.expense_account_code expacc,
                            rec_c1.itemcode
                       FROM mtl_system_items_b msib,
                            gl_code_combinations glcc
                      WHERE 1 = 1
                        AND msib.expense_account = glcc.code_combination_id
                        AND msib.segment1 = rec_c1.itemcode)
      LOOP
         DBMS_OUTPUT.put_line (   rec_c2.oldccid
                               || ' - orgid is - '
                               || rec_c2.organization_id
                              );
         v_charge_ccid_id := NULL;
         DBMS_OUTPUT.put_line (   rec_c2.inventory_item_id
                               || ' - '
                               || rec_c2.organization_id
                               || ' - '
                               || rec_c2.itemcode
                               || ' - '
                               || rec_c2.oldccid
                              );
         fnd_file.put_line (fnd_file.output,
                               'inventory_item_id- '
                            || rec_c2.inventory_item_id
                            || 'organization_id- '
                            || rec_c2.organization_id
                            || 'itemcode- '
                            || rec_c2.itemcode
                            || 'oldccid-'
                            || rec_c2.oldccid
                           );
         INSERT INTO xxaytb_glcode_ccid_check
                     (segment1, segment2, segment3,
                      segment4, segment5, segment6,
                      oldccid, itemcode,
                      orgid, expense_account
                     )
              VALUES (rec_c2.segment1, rec_c2.segment2, rec_c2.segment3,
                      rec_c2.segment4, rec_c2.segment5, rec_c2.segment6,
                      rec_c2.oldccid, rec_c2.itemcode,
                      rec_c2.organization_id, rec_c2.expacc
                     );
         BEGIN
            v_charge_ccid_id :=
               fnd_flex_ext.get_ccid
                                 (application_short_name      => 'SQLGL',
                                  key_flex_code               => 'GL#',
                                  structure_number            => 50368,
                                  validation_date             => TO_CHAR
                                                                    (SYSDATE,
                                                                     'DD-MON-YYYY'
                                                                    ),
                                  concatenated_segments       =>    rec_c2.segment1
                                                                 || '.'
                                                                 || rec_c2.segment2
                                                                 || '.'
                                                                 || rec_c2.segment3
                                                                 || '.'
                                                                 || rec_c2.expacc
                                                                 || '.'
                                                                 || rec_c2.segment5
                                                                 || '.'
                                                                 || rec_c2.segment6
                                 );
            COMMIT;
            DBMS_OUTPUT.put_line ('v_charge_ccid_id is:= ' || v_charge_ccid_id);
         END;
         IF v_charge_ccid_id > 0
         THEN
            INSERT INTO mtl_system_items_interface
                        (inventory_item_id, organization_id,
                         segment1, process_flag, transaction_type,
                         set_process_id,
                         expense_account
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode, 1, 'UPDATE'               --'CREATE'
                                                     ,
                         mtl_system_items_intf_sets_s.NEXTVAL,
                         v_charge_ccid_id
                        );
            COMMIT;
            INSERT INTO xxaytb_glcode_update_status
                        (inventory_item_id, organization_id,
                         itemcode,
                         msib_currval,
                         code_combination_id, status
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode,
                         mtl_system_items_intf_sets_s.CURRVAL,
                         v_charge_ccid_id, 'Success'
                        );
            COMMIT;
         ELSE
            INSERT INTO xxaytb_glcode_update_status
                        (inventory_item_id, organization_id,
                         itemcode,
                         msib_currval,
                         code_combination_id, status
                        )
                 VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
                         rec_c2.itemcode,
                         mtl_system_items_intf_sets_s.CURRVAL,
                         v_charge_ccid_id, 'Error'
                        );
            COMMIT;
         END IF;
         DBMS_OUTPUT.put_line ('---------------');
      END LOOP;
   END LOOP;
END;
/

Regisatered the concurrent program for the above procedure & submit the program.

Once completed Run the Import items program from inventory Responsibility.


 

Language set in toad

BEGIN
fnd_client_info.set_org_context('185');DBMS_SESSION.set_nls ('NLS_LANGUAGE', 'AMERICAN');
end;