Script to Attach Values to Lookup of Access Level "SYSTEM"

DECLARE
   ln_rowid    VARCHAR2 (1000);
   ln_rowid1   VARCHAR2 (1000);
BEGIN
 
   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,
                                     x_lookup_type              => 'MTL_RELATIONSHIP_TYPES',
                                     x_security_group_id        => 0,
                                     x_view_application_id      => 700,
                                     x_lookup_code              => '115',
                                     x_tag                      => NULL,
                                     x_attribute_category       => NULL,
                                     x_attribute1               => NULL,
                                     x_attribute2               => NULL,
                                     x_attribute3               => NULL,
                                     x_attribute4               => NULL,
                                     x_enabled_flag             => 'Y',
                                     x_start_date_active        => TO_DATE ('14-NOV-2013',
                                                                            'DD-MON-YYYY'
                                                                           ),
                                     x_end_date_active          => NULL,
                                     x_territory_code           => NULL,
                                     x_attribute5               => NULL,
                                     x_attribute6               => NULL,
                                     x_attribute7               => NULL,
                                     x_attribute8               => NULL,
                                     x_attribute9               => NULL,
                                     x_attribute10              => NULL,
                                     x_attribute11              => NULL,
                                     x_attribute12              => NULL,
                                     x_attribute13              => NULL,
                                     x_attribute14              => NULL,
                                     x_attribute15              => NULL,
                                     x_meaning                  => 'Optical-Drive',
                                     x_description              => 'Optical-Drive',
                                     x_creation_date            => SYSDATE,
                                     x_created_by               => 10338,
                                     x_last_update_date         => SYSDATE,
                                     x_last_updated_by          => 10338,
                                     x_last_update_login        => -1
                                    );
   DBMS_OUTPUT.put_line (ln_rowid1);
   COMMIT;
END;

Query to find Parameters and Value Sets associated with a Concurrent Program

1. SELECT
2. fcpl.user_concurrent_program_name "Concurrent Program Name",
3. fcp.concurrent_program_name "Short Name",
4. fdfcuv.column_seq_num "Column Seq Number",
5. fdfcuv.end_user_column_name "Parameter Name",
6. fdfcuv.form_left_prompt "Prompt",
7. fdfcuv.enabled_flag " Enabled Flag",
8. fdfcuv.required_flag "Required Flag",
9. fdfcuv.display_flag "Display Flag",
10. fdfcuv.flex_value_set_id "Value Set Id",
11. ffvs.flex_value_set_name "Value Set Name",
12. flv.meaning "Default Type",
13. fdfcuv.DEFAULT_VALUE "Default Value"
14. FROM
15. fnd_concurrent_programs fcp,
16. fnd_concurrent_programs_tl fcpl,
17. fnd_descr_flex_col_usage_vl fdfcuv,
18. fnd_flex_value_sets ffvs,
19. fnd_lookup_values flv
20. WHERE
21. fcp.concurrent_program_id = fcpl.concurrent_program_id
22. AND fcpl.user_concurrent_program_name = :conc_prg_name
23. AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
24. || fcp.concurrent_program_name
25. AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
26. AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
27. AND flv.lookup_code(+) = fdfcuv.default_type
28. AND fcpl.LANGUAGE = USERENV ('LANG')
29. AND flv.LANGUAGE(+) = USERENV ('LANG')
30. ORDER BY fdfcuv.column_seq_num;

Date convert Function

Have created very useful function to convert date format.

We are using it to take the date format from user's preferences and changing it to standard format.




CREATE OR REPLACE FUNCTION APPS.XX_DATE_CONVERT (p_date_format     IN VARCHAR2,
                                               P_PROMISED_DATE      VARCHAR2)
   RETURN VARCHAR2
IS
   p_return_promise_date   VARCHAR2 (100);
BEGIN
   IF p_date_format = 'MM-DD-RRRR'
   THEN
      p_return_promise_date :=
         TO_DATE(TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM-DD-RRRR HH24:MI:SS'),
                          'DD-MON-RRRR'));
   ELSIF p_date_format = 'DD-MM-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MM-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM/DD/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM/DD/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM.DD.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM.DD.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD-MON-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MON-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MM.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MM.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MM/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MM/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MON/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MON/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MON.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MON.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MM-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MM-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MON-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MON-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MON/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MON/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MM/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MM/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MM.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MM.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MON.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MON.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   END IF;

   RETURN p_return_promise_date;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
      );
END;
/

This can be used like :-

XXNHR_DATE_CONVERT (fnd_profile.value('ICX_DATE_FORMAT_MASK'),:PO_SHIPMENTS.PROMISED_DATE)    

API to create credit Memo & Apply it to the Invoice

SET SERVEROUTPUT ON;

DECLARE
   -- This script was tested in 11i instance --
   v_return_status          VARCHAR2(1);
   p_count                  NUMBER;
   v_msg_count              NUMBER;
   v_msg_data               VARCHAR2(2000);
   v_request_id             NUMBER;
   v_context                VARCHAR2(2);
   l_cm_lines_tbl           arw_cmreq_cover.cm_line_tbl_type_cover;
   l_customer_trx_id        NUMBER;
   cm_trx_id                NUMBER;
   v_interface_header_rec   arw_cmreq_cover.pq_interface_rec_type;
   ind                      NUMBER;
   l_trx_number             VARCHAR2(30);

   CURSOR c_inv(p_trx_number VARCHAR2)
   IS
      SELECT rct.trx_number
           ,  rct.customer_trx_id
           ,  rctl.customer_trx_line_id
           ,  rctl.quantity_invoiced
           ,  unit_selling_price
      FROM   ra_customer_trx_all rct, ra_customer_trX_lines_all rctl
      WHERE  rct.customer_trx_id = rctl.customer_trx_id
      AND    trx_number = p_trx_number
      AND    line_type = 'LINE';

   PROCEDURE set_context
   IS
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info(0);
      MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
   END set_context;
BEGIN
   -- Setting the context ----
   set_context;

   DBMS_OUTPUT.put_line('Invoking Credit Memo Creation process');

   l_trx_number := '20116773';

   FOR lc_inv IN c_inv(l_trx_number)
   LOOP
      ind := 1;
      l_customer_trx_id := lc_inv.customer_trx_id;

      l_cm_lines_tbl(ind).customer_trx_line_id := lc_inv.customer_trx_line_id;
      l_cm_lines_tbl(ind).quantity_credited := lc_inv.quantity_invoiced * -1;
      l_cm_lines_tbl(ind).price := lc_inv.unit_selling_price;
      l_cm_lines_tbl(ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;
   END LOOP;

   ar_credit_memo_api_pub.create_request( -- standard api parameters
                                         p_api_version                  => 1.0
                                       ,  p_init_msg_list                => fnd_api.g_true
                                       ,  p_commit                       => fnd_api.g_false
                                       -- credit memo request parameters
                                       ,  p_customer_trx_id                => l_customer_trX_id
                                       ,  p_line_credit_flag             => 'Y'
                                       ,  P_CM_LINE_TBL                  => l_cm_lines_tbl
                                       ,  p_cm_reason_code               => 'RETURN'
                                       ,  p_skip_workflow_flag           => 'Y'
                                       ,  p_batch_source_name            => 'XX_ORDER_ENTRY'
                                       ,  p_interface_attribute_rec      => v_interface_header_rec
                                       ,  p_credit_method_installments   => NULL
                                       ,  p_credit_method_rules          => NULL
                                       ,  x_return_status                => v_return_status
                                       ,  x_msg_count                    => v_msg_count
                                       ,  x_msg_data                     => v_msg_data
                                       ,  x_request_id                   => v_request_id);
   DBMS_OUTPUT.put_line('Message count ' || v_msg_count);

   IF v_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line('l_msg_data ' || v_msg_data);
   ELSIF v_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         v_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

         IF v_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line('Message' || p_count || ' ---' || v_msg_data);
      END LOOP;
   END IF;

   IF v_return_status <> 'S'
   THEN
      DBMS_OUTPUT.put_line('Failed');
   ELSE
      SELECT cm_customer_trx_id
      INTO   cm_trx_id
      FROM   ra_cm_requests_all
      WHERE  request_id = v_request_id;

      DBMS_OUTPUT.put_line(' CM trx_id = ' || cm_trx_id);
   -- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
   -- COMMIT;
   END IF;
END;

Oracle R12 documentation
------------------------------

http://docs.oracle.com/cd/B34956_01/current/html/docset.html

Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL --------------------------------------------------------------------------------- SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h where 1=1 and interface_header_context = 'ORDER ENTRY' and interface_header_attribute1 = to_char(h.order_number) SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num, rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line from RA_CUSTOMER_TRX_LINES_ALL rl, oe_order_lines_all l, oe_order_headers_all h where line_type = 'LINE' and interface_line_context = 'ORDER ENTRY' and h.header_id = l.header_id and interface_line_attribute6 = to_char(l.line_id) and interface_line_attribute1 = to_char(h.order_number) and sales_order = h.order_number SELECT rl.interface_status,rl.line_number Invoice_line_num, h.order_number, l.line_id from RA_INTERFACE_LINES_ALL rl, oe_order_lines_all l, oe_order_headers_all h where line_type = 'LINE' and interface_line_context = 'ORDER ENTRY' and h.header_id = l.header_id and interface_line_attribute6 = to_char(l.line_id) and interface_line_attribute1 = to_char(h.order_number) and sales_order_line IS NOT NULL;

Script to read data from comma separated file and insert into a custom table

/*============================================================================+
|
+=============================================================================+
| |
| File Name : NAPP_GLOBAL_RESP_FIX.sql |
| Object Name : NAPP_GLOBAL_RESP_FIX.sql |
| |
| Description : |
| |
| Revision History : |
| |
| Ver Date Name Revision Description |
| === ========= ============== ========================================== |
| 1.0 03-DEC-2010 Shekhar Created the program |
+=============================================================================*/
SET PAGESIZE 0;
SET LINESIZE 10000;
SET SERVEROUTPUT ON SIZE 100000;
SET TRIMSPOOL ON;
SET DEFINE OFF;


DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_eof CHAR;
l_user_name VARCHAR2 (150);
l_resp_name VARCHAR2 (500);
l_text VARCHAR2 (5000);
l_start_date DATE;
l_start_date_str VARCHAR2(50);
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_resp_start_date date;
l_message varchar2(100);
l_dir_name varchar2(100);
l_item varchar2(100);
l_org varchar2(100);
c1 number;
c2 number;
l_value varchar2(32);

BEGIN

BEGIN

select name into l_dir_name from v$database;
dbms_output.put_line('UTL file directory '||'/usr/tmp/'||l_dir_name);

END;

Fnd_Global.apps_initialize (1429,20420,1);
l_eof := 'N';
l_file_handle :=
UTL_FILE.FOPEN
('/usr/tmp/'||l_dir_name
,'item_cat_value.csv'
,'r'
);

LOOP
-- l_user_id := -1;
-- l_resp_id := -1;
-- l_resp_appl_id := -1;
l_message:='SUCCESS';
-- l_resp_start_date := null;

BEGIN
UTL_FILE.GET_LINE (l_file_handle, l_text);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_eof := 'Y';
END;

EXIT WHEN l_eof = 'Y';

c1:=instr(l_text,',');
c2:=instr(l_text,',',c1+1);

l_item := SUBSTR (l_text, 1, INSTR (l_text, ',') - 1);
l_org := REPLACE(SUBSTR (l_text
, c1 + 1
, c2 -c1
),',','');
l_value := SUBSTR (l_text,c2+1,length(l_text));

BEGIN

INSERT INTO napp_item_issue(item,org,napp_intransit) VALUES (l_item,l_org,l_value);


COMMIT;

Exception when OTHERS then

-- write_log ('ERROR: User name ' || l_user_name || 'do not exist');
l_message:='FAILED:VALIDATION';

END;

-- IF l_user_id=-1 OR l_resp_id=-1 OR l_resp_appl_id=-1 OR l_resp_start_date is NULL then

-- l_message:='FAILED:DATA MISSING';

-- ELSE

-- BEGIN

/* fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id
,responsibility_id => l_resp_id
,responsibility_application_id => l_resp_appl_id
,security_group_id => 0
,start_date => l_resp_start_date
,end_date => NULL
,description => l_resp_name
,update_who_columns => 'Y'
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_message:='FAILED:API';
END ;

END IF;

dbms_output.put_line(l_user_name||'|'||l_resp_name||'|'||l_message);
*/

END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('When others ' || SQLERRM);
RAISE;

END;
/