Sales Order Pick Status Lookup Value Query

Sales Order Pick Status Lookup Value Query


SELECT typ.lookup_type, typ.meaning lookup_type_meaning,
       typ.description lookup_type_description, flv_released.lookup_code,
       flv_released.meaning, flv_released.description
  FROM fnd_lookup_values flv_released, fnd_lookup_types_tl typ
 WHERE flv_released.lookup_type = 'PICK_STATUS'
   AND flv_released.LANGUAGE = USERENV ('LANG')
   AND flv_released.view_application_id = 665
   AND flv_released.security_group_id = 0
   AND flv_released.lookup_type = typ.lookup_type

Query to find Unaccounted Receipts with Acct Nature is Average Costing

Unaccounted Receipts with Acct Nature is Average Costing


SELECT DISTINCT jrh.receipt_num, jrh.creation_date, jair.shipment_line_id,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id = jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE') excise,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                 jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_EDUCATION_CESS')
                                                        excise_education_cess,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                    jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_SH_EDU_CESS')
                                                           excise_sh_edu_cess
           FROM jai_rcv_headers jrh,
                jai_rcv_lines jair,
                jai_rcv_line_taxes jtax
          WHERE jrh.online_claim_flag = 'Y'
            AND jair.shipment_header_id = jrh.shipment_header_id
            AND jair.online_claim_flag = 'Y'
            AND jtax.shipment_header_id = jrh.shipment_header_id
            AND jtax.shipment_line_id = jair.shipment_line_id
            AND jrh.organization_id = :P_ORGN_ID
            AND TRUNC (jrh.creation_date) BETWEEN :p_from_date AND :p_to_date
            --AND jrh.receipt_num = '961664'          -- IN ('961033', '961664')
            AND EXISTS (
                   SELECT '1'
                     FROM jai_rcv_journal_entries
                    WHERE shipment_line_id = jtax.shipment_line_id
                      AND acct_nature = 'Average Costing'
                      AND organization_code =:P_ORGN_CODE
                      AND transaction_type = 'DELIVER'
                      AND TRUNC (transaction_date) BETWEEN :p_from_date
                                                       AND :p_to_date
                      AND jrh.receipt_num NOT IN (
                             SELECT jrj.receipt_num
                               FROM jai_rcv_journal_entries jrj
                              WHERE acct_nature ='CENVAT'
                                AND jrj.organization_code = :P_ORGN_CODE 
                                AND transaction_type = 'RECEIVE'
                                -- AND jrj.receipt_num IN (961033, 961664)
                                AND TRUNC (transaction_date)
                                       BETWEEN :p_from_date
                                           AND :p_to_date))
            AND UPPER (jtax.tax_type) IN
                    ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS')

CREATE MATERIALIZED VIEW IN ORACLE

DROP MATERIALIZED VIEW apps.XX_TEST_V_MV;

CREATE MATERIALIZED VIEW apps.XX_TEST_V_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from xx_emp 

Manually Refresh Materialized View n Oracle

Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle

execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');

Parameters of Procedure 

REFRESH

The first parameter to the procedure 
REFRESH is the name of the materialized view or snapshot, the second parameter 
specifies the type of refresh.




Type 
of Refresh 
Description

F, f 
Fast Refresh

C, c 
Complete Refresh

A 
Always perform complete refresh

? 
Use the default option





The manual refresh overtakes any previous refresh timing options, which were specified during the creation of the view. It 
more specifically overrides the 'start with' clause, which is specified with the 'create materialized view' command.


Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, 

which were defined using the automatic refreshes.



SQL>  execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.

Descriptive flex field, DFF, Non editable

Descriptive flex field, DFF, Non editable

To make some attributes Read-only in Descriptive Flex Field Oracle Apps.


Pls do the following steps.


First Create Value Set as per below Screen Shots

Navigate to Application Developer --> Flex Field --> Descriptive --> Segments 


Do the changes as per the below screen shots

Now you can check the Attribute Values in Oracle Apps Receivables Transaction Screen

How do i get the last generated Sequence number in Oracle

How do i get the last generated Sequence number in Oracle



SELECT fds.doc_sequence_id, fds.NAME, ds.sequence_name, fds.application_id,
       fds.audit_table_name, fds.TYPE, fds.table_name, fds.initial_value,
       ds.last_number
  FROM fnd_document_sequences fds, dba_sequences ds
 WHERE ds.sequence_name = fds.db_sequence_name
   AND ds.sequence_name = 'FND_DOC_SEQ_2116_S'

Oracle Applications: Script to End Date Responsibility for a User

To End Date Responsibility in Oracle Apps



CREATE OR REPLACE PROCEDURE APPS.RESPONSIBILITY_END_DATE (
   retcode           OUT      VARCHAR2,
   errbuf            OUT      VARCHAR2,
   p_responsibilty   IN       VARCHAR2,
   action_type       IN       VARCHAR2,
   p_end_date                 varchar2
)
IS
   CURSOR c1
   IS
      SELECT DISTINCT u.user_id user_id, u.user_name usr_name,
                      wur.role_orig_system_id responsibility_id,
                      resp.responsibility_name,
                      (SELECT application_id
                         FROM fnd_application
                        WHERE application_short_name =
                                 /* Val between 1st and 2nd separator */
                                 REPLACE
                                    (SUBSTR (wura.role_name,
                                               INSTR (wura.role_name,
                                                      '|',
                                                      1,
                                                      1
                                                     )
                                             + 1,
                                             (  INSTR (wura.role_name,
                                                       '|',
                                                       1,
                                                       2
                                                      )
                                              - INSTR (wura.role_name,
                                                       '|',
                                                       1,
                                                       1
                                                      )
                                              - 1
                                             )
                                            ),
                                     '%col',
                                     ':'
                                    )) responsibility_application_id,
                      (SELECT security_group_id
                         FROM fnd_security_groups
                        WHERE security_group_key =
                                 /* Val after 3rd separator */
                                 REPLACE
                                    (SUBSTR (wura.role_name,
                                               INSTR (wura.role_name,
                                                      '|',
                                                      1,
                                                      3
                                                     )
                                             + 1
                                            ),
                                     '%col',
                                     ':'
                                    )) security_group_id,
                      wura.start_date, wura.end_date end_date,
                      wura.created_by created_by,
                      wura.creation_date creation_date,
                      wura.last_updated_by last_updated_by,
                      wura.last_update_date last_update_date,
                      wura.last_update_login last_update_login,
                      wura.assignment_reason description
                 FROM fnd_user u,
                      wf_user_role_assignments wura,
                      wf_all_user_roles wur,
                      fnd_responsibility_vl resp
                WHERE u.user_name = wura.user_name
                  AND wura.relationship_id = -1
                  AND wur.role_orig_system = 'FND_RESP'
                  AND NOT wura.role_name LIKE 'FND_RESP|%|ANY'
                  AND wura.role_name = wur.role_name
                  AND wura.user_name = wur.user_name
                  AND wur.role_orig_system_id = resp.responsibility_id
                  AND UPPER (SUBSTR (resp.responsibility_name, 1, 3)) =p_responsibilty
                  AND wura.end_date IS NULL;


   CURSOR c2
   IS
      SELECT DISTINCT u.user_id user_id, u.user_name usr_name,
                      wur.role_orig_system_id responsibility_id,
                      resp.responsibility_name,
                      (SELECT application_id
                         FROM fnd_application
                        WHERE application_short_name =
                                 /* Val between 1st and 2nd separator */
                                 REPLACE
                                    (SUBSTR (wura.role_name,
                                               INSTR (wura.role_name,
                                                      '|',
                                                      1,
                                                      1
                                                     )
                                             + 1,
                                             (  INSTR (wura.role_name,
                                                       '|',
                                                       1,
                                                       2
                                                      )
                                              - INSTR (wura.role_name,
                                                       '|',
                                                       1,
                                                       1
                                                      )
                                              - 1
                                             )
                                            ),
                                     '%col',
                                     ':'
                                    )) responsibility_application_id,
                      (SELECT security_group_id
                         FROM fnd_security_groups
                        WHERE security_group_key =
                                 /* Val after 3rd separator */
                                 REPLACE
                                    (SUBSTR (wura.role_name,
                                               INSTR (wura.role_name,
                                                      '|',
                                                      1,
                                                      3
                                                     )
                                             + 1
                                            ),
                                     '%col',
                                     ':'
                                    )) security_group_id,
                      wura.start_date, wura.end_date end_date,
                      wura.created_by created_by,
                      wura.creation_date creation_date,
                      wura.last_updated_by last_updated_by,
                      wura.last_update_date last_update_date,
                      wura.last_update_login last_update_login,
                      wura.assignment_reason description
                 FROM fnd_user u,
                      wf_user_role_assignments wura,
                      wf_all_user_roles wur,
                      fnd_responsibility_vl resp
                WHERE u.user_name = wura.user_name
                  AND wura.relationship_id = -1
                  AND wur.role_orig_system = 'FND_RESP'
                  AND NOT wura.role_name LIKE 'FND_RESP|%|ANY'
                  AND wura.role_name = wur.role_name
                  AND wura.user_name = wur.user_name
                  --AND u.user_name = p_user_name
                  AND wur.role_orig_system_id = resp.responsibility_id
                  AND UPPER (SUBSTR (resp.responsibility_name, 1, 3)) = p_responsibilty
                  AND TRUNC(wura.end_date) =to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS');


   num_total_rows   NUMBER;
   l_action         VARCHAR2 (10);
   resp_end_date varchar2(15);
BEGIN
   num_total_rows := 0;
   l_action := UPPER (action_type);
   
   
     
     -- fnd_file.put_line (fnd_file.LOG, 'User Name --> ' || p_user_name);
   
   DBMS_OUTPUT.put_line ('Responsibility End Date Before Conversion.....' || p_end_date);
   
   resp_end_date := to_char(to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-RRRR');
   
  -- resp_end_date := FND_DATE.CANONICAL_TO_DATE (p_end_date);
   
   DBMS_OUTPUT.put_line ('Responsibility End Date.....' || p_end_date);
      fnd_file.put_line (fnd_file.LOG, 'Responsibility End Date.....' || p_end_date);


   IF (l_action = 'DISABLE')
   THEN
      DBMS_OUTPUT.put_line ('Action Type.....' || l_action);
      fnd_file.put_line (fnd_file.LOG, 'Action Type.....' || l_action);


      FOR i IN c1
      LOOP
         DBMS_OUTPUT.put_line ('Processing Started.....');
         fnd_file.put_line (fnd_file.LOG, 'Processing Started.....');
         DBMS_OUTPUT.put_line (   'Processing Responsibility ==> '
                               || i.responsibility_name
                              );
         fnd_file.put_line (fnd_file.LOG,
                               'Processing Responsibility ==> '
                            || i.responsibility_name
                           );
         l_action := UPPER (action_type);


         IF (fnd_user_resp_groups_api.assignment_exists
                                             (i.user_id,
                                              i.responsibility_id,
                                              i.responsibility_application_id,
                                              i.security_group_id
                                             )
            )
         THEN
            fnd_user_resp_groups_api.update_assignment
               (user_id                            => i.user_id,
                responsibility_id                  => i.responsibility_id,
                responsibility_application_id      => i.responsibility_application_id,
                security_group_id                  => i.security_group_id,
                start_date                         => i.start_date,
                end_date                           => to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS'),
                description                        => NULL
               );
         END IF;


         COMMIT;
         num_total_rows := c1%ROWCOUNT;
         DBMS_OUTPUT.put_line ('Processing Completed Successfully.....');
         fnd_file.put_line (fnd_file.LOG,
                            'Processing Completed Successfully.....'
                           );
      END LOOP;


      IF num_total_rows > 0
      THEN
         --DBMS_OUTPUT.new_line;
         DBMS_OUTPUT.put_line (   'Total Rows Updated = '
                               || TO_CHAR (num_total_rows)
                              );
         fnd_file.put_line (fnd_file.LOG, 'Rows Updated:' || num_total_rows);
      ELSE
         fnd_file.put_line (fnd_file.LOG,
                            'There is no records available to update'
                           );
         DBMS_OUTPUT.put_line ('There is no records available to update');
      END IF;
   END IF;


   IF (l_action = 'ENABLE')
   THEN
      DBMS_OUTPUT.put_line ('Action Type.....' || l_action);
      fnd_file.put_line (fnd_file.LOG, 'Action Type.....' || l_action);


      FOR j IN c2
      LOOP
         DBMS_OUTPUT.put_line ('Processing Started.....');
         fnd_file.put_line (fnd_file.LOG, 'Processing Started.....');
         DBMS_OUTPUT.put_line (   'Processing Responsibility ==> '
                               || j.responsibility_name
                              );
         fnd_file.put_line (fnd_file.LOG,
                               'Processing Responsibility ==> '
                            || j.responsibility_name
                           );


         IF (fnd_user_resp_groups_api.assignment_exists
                                             (j.user_id,
                                              j.responsibility_id,
                                              j.responsibility_application_id,
                                              j.security_group_id
                                             )
            )
         THEN
            fnd_user_resp_groups_api.update_assignment
               (user_id                            => j.user_id,
                responsibility_id                  => j.responsibility_id,
                responsibility_application_id      => j.responsibility_application_id,
                security_group_id                  => j.security_group_id,
                start_date                         => j.start_date,
                end_date                           => NULL,
                description                        => NULL
               );
         END IF;


         COMMIT;
         num_total_rows := c2%ROWCOUNT;
         DBMS_OUTPUT.put_line ('Processing Completed Successfully.....');
         fnd_file.put_line (fnd_file.LOG,
                            'Processing Completed Successfully.....'
                           );
      END LOOP;


      IF num_total_rows > 0
      THEN
         --DBMS_OUTPUT.new_line;
         DBMS_OUTPUT.put_line (   'Total Rows Updated = '
                               || TO_CHAR (num_total_rows)
                              );
         fnd_file.put_line (fnd_file.LOG, 'Rows Updated:' || num_total_rows);
      ELSE
         fnd_file.put_line (fnd_file.LOG,
                            'There is no records available to update'
                           );
         DBMS_OUTPUT.put_line ('There is no records available to update');
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG,
                            'Error Code is --> '
                         || SQLCODE
                         || ' And Error Msg is --> '
                         || SQLERRM
                        );
END;
/

Here i have not specified any Username to end date the responsibility. If u want to end date user-wise responsibility,  You have to pass the User name as the parameter in the procedure and you have to specify the username as a parameter in Cursor also. 

So it will end date the responsibility.................

Deleted Records Backup script using Trigger in Oracle

Deleted Records Backup Script using Trigger in Oracle

Create Table Script to Backup the Deleted Records
CREATE TABLE xx_trg_test(trx_id NUMBER, trx_number VARCHAR2(30),trx_date DATE, delete_date date, deleted_by VARCHAR2(30)) 

Delete Script to Delete the Records
delete from ra_customer_trx_all where  CUSTOMER_TRX_ID=1083


Sample trigger for Backup the Deleted records after Deleting
CREATE OR REPLACE TRIGGER trx_orders_before_delete
   BEFORE DELETE
   ON ra_customer_trx_all
   FOR EACH ROW
DECLARE
   v_username   VARCHAR2 (10);
BEGIN
   -- Find username of person performing the DELETE on the table
   SELECT USER
     INTO v_username
     FROM DUAL;

   -- Insert record into audit table
   INSERT INTO xx_trg_test
               (trx_id, trx_number, trx_date,
                delete_date, deleted_by
               )
        VALUES (:OLD.customer_trx_id, :OLD.trx_number, :OLD.trx_date,
                SYSDATE, v_username
               );
END;

Check the original table for Deleted Records  

Select * from ra_customer_trx_all where  CUSTOMER_TRX_ID=1083  


Check the Deleted Records in Custom Table

Select * from xx_trg_test  

Anonymous PL/SQL Block to update records using cursors in Oracle Apps

Anonymous PL/SQL Block to update records using cursors in Oracle Apps


DECLARE
   CURSOR c1
   IS
      SELECT xxrs.shipmentline_id, xxrs.amount, jrl.tax_amount
        FROM xxcust_table xxrs, jai_rcv_lines jrl
       WHERE xxrs.shipmentline_id = jrl.shipment_line_id;


   num_total_rows   NUMBER;
BEGIN
   FOR i IN c1
   LOOP
      UPDATE jai_rcv_lines
         SET tax_amount = i.amount
       WHERE shipment_line_id = i.shipmentline_id;


      COMMIT;
      num_total_rows := c1%ROWCOUNT;
   END LOOP;


   DBMS_OUTPUT.put_line ('Total No of Rows Updated : ' || num_total_rows);
END;

Oracle Applications: Third Party Invoice Query in Oracle Apps R12

Third Party Invoice Query in Oracle Apps

SELECT rsh.receipt_num, TRUNC (rsh.creation_date) receipt_date,
       jrti.invoice_num, aia.invoice_date, aia.gl_date, jrti.invoice_id,
       pov.vendor_name, pvs.vendor_site_code, jrtid.line_number,
       jrtid.tax_type, jrtid.tax_rate, jrtid.tax_amount
  FROM jai_rcv_tp_invoices jrti,
       jai_rcv_tp_inv_details jrtid,
       rcv_shipment_headers rsh,
       po_vendors pov,
       po_vendor_sites_all pvs,
       ap_invoices_all aia
 WHERE jrti.batch_invoice_id = jrtid.batch_invoice_id
   AND rsh.shipment_header_id = jrti.shipment_header_id
   AND jrti.vendor_id = pov.vendor_id
   AND pov.vendor_id = pvs.vendor_id
   AND pvs.vendor_site_id = jrti.vendor_site_id
   AND aia.invoice_id = jrti.invoice_id
   AND rsh.shipment_header_id = 1664789

Supplier Sourcing Rule Query in Oracle Apps

Supplier Sourcing Rule Query in Oracle Apps

SELECT msr.sourcing_rule_name, mas.assignment_set_name,
       mas.description assignment_desc, msra.entity_name item_code,
       v.SEGMENT1 vendor_code,
       v.vendor_name, msso.vendor_site_id, vs.vendor_site_code,
       msso.source_type, msso.allocation_percent, msso.RANK
  FROM apps.mrp_sr_assignments_v msra,
       mrp_assignment_sets mas,
       mrp_sourcing_rules msr,
       mrp_sr_source_org msso,
       po_vendors v,
       po_vendor_sites_all vs,
       mrp_sr_receipt_org sro,
       mtl_interorg_ship_methods sm
 WHERE msr.sourcing_rule_id = msra.sourcing_rule_id
   AND msra.assignment_set_id = mas.assignment_set_id
   AND mas.assignment_set_id = 1
   AND mas.assignment_set_name  like '%ASSIGNMENT_SET%'
   AND v.vendor_id(+) = msso.vendor_id
   AND vs.vendor_site_id(+) = msso.vendor_site_id
   AND msr.sourcing_rule_id = sro.sourcing_rule_id
   AND sm.to_organization_id(+) = sro.receipt_organization_id
   AND sro.sr_receipt_id = msso.sr_receipt_id
   AND msso.ship_method IS NULL
   and v.segment1=:P_vendor_code   -- AB TOOLS PVT. LTD.

Get Request Group name Associated With Application

Get Request Group name Associated With Application



SELECT   g.request_group_name, c.user_concurrent_program_name,
         a.application_name, g.application_id, g.request_group_id,
         u.unit_application_id, u.request_unit_id
    FROM fnd_request_groups g,
         fnd_request_group_units u,
         fnd_application_tl a,
         fnd_concurrent_programs_tl c
   WHERE g.request_group_id = u.request_group_id
     AND u.unit_application_id = a.application_id
     AND u.request_unit_id = c.concurrent_program_id
ORDER BY c.user_concurrent_program_name,
         a.application_name,
         g.request_group_id

         

GL Balance Query in Oracle Apps

GL Balance Query in Oracle Apps

SELECT   gb.period_name "Period Name", gb.period_year "Period Year",gb.period_Num "Period Num",
         gb.set_of_books_id "SOB ID",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4 "Account",
         gb.currency_code "Currency", SUM (gb.period_net_dr) "Period Net Dr",
         SUM (gb.period_net_cr) "Period Net Cr",
         SUM ((gb.period_net_dr - gb.period_net_cr)) "PTD"
    FROM gl_balances gb, gl_code_combinations gcc
   WHERE gb.set_of_books_id = 10
--     AND gb.period_name = 'FEB-10-11'
     AND gb.currency_code = 'INR'
     AND gcc.segment4 = '13141'
     AND gb.code_combination_id = gcc.code_combination_id
GROUP BY gb.period_name,
         gb.period_year,
         gb.PERIOD_NUM,
         gb.set_of_books_id,
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4,
         gb.currency_code
Order by gb.PERIOD_YEAR, gb.PERIOD_NUM

To get Ap Invoice Status in Oracle Apps

To get Ap Invoice Status in Oracle Apps

AP_INVOICES_UTILITY_PKG.get_payment_status( P_invoice_id )

India Local Payables Tax type

Query to find out the localization Tax Type for Purchasing Module


SELECT typ.lookup_type tax_lookup_type, typ.meaning tax_lookup_type_meaning,
       typ.description tax_lookup_type_desc, val.lookup_code tax_type,
       val.meaning tax_type_meaning, val.description tax_type_desc,
       val.enabled_flag, val.start_date_active, val.end_date_active
  FROM fnd_lookup_types_vl typ, fnd_lookup_values_vl val
 WHERE typ.lookup_type = val.lookup_type
   AND val.lookup_type = 'JAI_TAX_TYPE'
   AND val.end_date_active IS NULL
   AND val.enabled_flag = 'Y'

Item Classification Query in Oracle Apps

SELECT   *
    FROM (SELECT 'q1' query1, msi.organization_id, msi.inventory_item_id,
                 msi.segment1 item_code, msi.description item_desc,
                 tmpl.template_name, tmpl.description, tmpl.regime_code,
                 att.attribute_code, att.attribute_value
            FROM jai_rgm_tmpl_itm_regns tmp_itm,
                 jai_rgm_tmpl_org_regns tmp_org,
                 jai_rgm_itm_templates tmpl,
                 jai_rgm_itm_tmpl_attrs att,
                 mtl_system_items_b msi
           WHERE tmp_itm.templ_org_regns_id = tmp_org.templ_org_regns_id
             AND tmpl.template_id = tmp_org.template_id
             AND tmpl.template_id = att.template_id
             AND msi.inventory_item_id = tmp_itm.inventory_item_id
             AND msi.organization_id = tmp_org.organization_id
             AND tmp_org.organization_id = :P_ORGN_ID
            AND tmp_itm.inventory_item_id = :P_ITEM_ID
          UNION ALL
          SELECT 'q2' query2, msi.organization_id, msi.inventory_item_id,
                 msi.segment1 item_code, msi.description item_desc,
                 'tmpl' template_name, NULL description, rgm.regime_code,
                 tmpl.attribute_code, tmpl.attribute_value
            FROM jai_rgm_itm_regns rgm,
                 jai_rgm_itm_tmpl_attrs tmpl,
                 mtl_system_items_b msi
           WHERE rgm.rgm_item_regns_id = tmpl.rgm_item_regns_id
             AND rgm.organization_id = :P_ORGN_ID
             --AND rgm.inventory_item_id =  :P_ITEM_ID
             AND msi.inventory_item_id = rgm.inventory_item_id
             AND rgm.organization_id = msi.organization_id)
ORDER BY inventory_item_id

How to create a table inside a stored procedure

Create Table Script Inside the Procedure in Oracle 


Sample Script 1 : 


CREATE OR REPLACE PROCEDURE prc_create_table
AS
v_ddl 
VARCHAR2 (100); 
BEGIN
v_ddl := 'CREATE TABLE temp_test(a number, b varchar2(10))'; 
EXECUTE IMMEDIATE v_ddl; 
END


for Test Case...........


Begin
    prc_create_table ();
End;


Sample Script 2 :  
  
Sample 2 :

create or replace procedure create_table as
begin
       execute immediate 'create table mytable (id integer )';
end ;


for Test Case........... 




Begin
create_table ();
End;

When we are running Concurrent Programs  Executable Type is PL/Sql Procedure with Date Parameter using FND_STANDARD_DATE as a Value set , That time might be we will get the following error.

ORA-01861: literal does not match format string

To avoid this error , Convert the date format as per the below format.


to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS')


Note: Define Datatype of Parameter is Varchar2.

Amount in Words Conversion in Oracle

Convert Amount into Words in Oracle 

CREATE OR REPLACE FUNCTION f_amount_to_words (p_amt IN NUMBER)

   RETURN VARCHAR2
IS
   m_main_amt_text     VARCHAR2 (2000);
   m_top_amt_text      VARCHAR2 (2000);
   m_bottom_amt_text   VARCHAR2 (2000);
   m_decimal_text      VARCHAR2 (2000);
   m_top               NUMBER (20, 5);
   m_main_amt          NUMBER (20, 5);
   m_top_amt           NUMBER (20, 5);
   m_bottom_amt        NUMBER (20, 5);
   m_decimal           NUMBER (20, 5);
   m_amt               NUMBER (20, 5);
   m_text              VARCHAR2 (2000);
BEGIN
   m_main_amt := NULL;
   m_top_amt_text := NULL;
   m_bottom_amt_text := NULL;
   m_decimal_text := NULL;
   -- To get paise part
   m_decimal := p_amt - TRUNC (p_amt);


   IF m_decimal > 0
   THEN
      m_decimal := m_decimal * 100;
   END IF;


   m_amt := TRUNC (p_amt);
   m_top := TRUNC (m_amt / 100000);
   m_main_amt := TRUNC (m_top / 100);
   m_top_amt := m_top - m_main_amt * 100;
   m_bottom_amt := m_amt - (m_top * 100000);


   IF m_main_amt > 0
   THEN
      m_main_amt_text := TO_CHAR (TO_DATE (m_main_amt, 'J'), 'JSP');


      IF m_main_amt = 1
      THEN
         m_main_amt_text := m_main_amt_text || ' CRORE ';
      ELSE
         m_main_amt_text := m_main_amt_text || ' CRORES ';
      END IF;
   END IF;


   IF m_top_amt > 0
   THEN
      m_top_amt_text := TO_CHAR (TO_DATE (m_top_amt, 'J'), 'JSP');


      IF m_top_amt = 1
      THEN
         m_top_amt_text := m_top_amt_text || ' LAKH ';
      ELSE
         m_top_amt_text := m_top_amt_text || ' LAKHS ';
      END IF;
   END IF;


   IF m_bottom_amt > 0
   THEN
      m_bottom_amt_text := TO_CHAR (TO_DATE (m_bottom_amt, 'J'), 'JSP');
   END IF;


   IF m_decimal > 0
   THEN
      IF NVL (m_bottom_amt, 0) + NVL (m_top_amt, 0) > 0
      THEN
         m_decimal_text :=
             ' AND ' || TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP')
             || ' Paise ';
      ELSE
         m_decimal_text :=
                        TO_CHAR (TO_DATE (m_decimal, 'J'), 'JSP')
                        || ' Paise ';
      END IF;
   END IF;


   m_text :=
      INITCAP (   m_main_amt_text
             || m_top_amt_text
             || m_bottom_amt_text
             || ' Rupees'
             || m_decimal_text
             || ' ONLY'
            );
   m_text := UPPER (SUBSTR (m_text, 1, 1)) || SUBSTR (m_text, 2);
   m_text := ' ' || m_text;
   RETURN (m_text);
END f_amount_to_words;


i.e
SELECT F_AMOUNT_TO_WORDS(155221.25) FROM DUAL; F_AMOUNT_TO_WORDS(155221.25)
Output is
 One Lakh Fifty-Five Thousand Two Hundred Twenty-One Rupees And Twenty-Five Paise  Only