Workflow tables in oracle apps

select * from wf_item_types where name = 'BWFINSER'

select * from wf_item_types_tl where name = 'BWFINSER'

select * from wf_items where item_type = 'BWFINSER'

select * from wf_item_attributes  where item_type = 'BWFINSER'

select * from wf_item_attributes_tl where item_type = 'BWFINSER'

select * from wf_activities where item_type = 'BWFINSER'

select * from wf_activities_tl

select * from wf_activity_attributes

select * from wf_activity_attributes_tl

select * from wf_activity_attr_values

select * from wf_messages

select * from wf_notifications where message_type = 'BWFINSER'

select * from wf_notification_Attributes

select * from wf_messages_tl

select * from wf_message_Attributes

select * from wf_message_Attributes_tl

select * from wf_process_Activities

select * from wf_lookup_types_tl

select * from wf_lookups_tl 

Open & Close Period in R12

General Ledger Periods

1. Navigate to General Ledger Super User->Setup->Open/Close, then the Ledger and click find.


2. Now open the required periods by clicking “Open Periods” button,select the required period as Target Period and click Open button.

Payables Period


1. Navigate to Payables Super User or Payable Manager->Accounting->Control Payables Periods , then open the periods by changing the Period Status and click on Save button.

Purchasing Periods

1. Navigate to Purchasing SuperUser->Setup->Financials->Accounting->Control Purchasing Periods , then enter the Fiscal year and click Go button.

(If it prompts for selecting Operating Unit,select it and click Go button.)


2. Then open the periods by changing the Period Status and click on Save button.

Inventory period

1. Navigate to Inventory SuperUser->Accounting Close Cycle->Inventory Accounting Periods and select the Inventory Org from LOV.


2. Select the period to be opened and click on Change Status button.

Register custom table in oracle application R12

Register Table 
procedure register_table
(p_appl_short_name in varchar2, –Custom Application Short Name
p_table_name in varchar2, –Table Name
p_table_type in varchar2, –Table type,Use ’T’ if it is a transaction table & ’S’ for a ”seed data” table
p_next_extent in number default 512,
p_pct_free in number default 10, –Percentage of space in each of the table’s blocks reserved for future updates to the table (1–99)
p_pct_used in number default 70); –Minimum percentage of used space in each data block of the table (1–99)
–The sum of p_pct_free and p_pct_used must be less than 100

Register Column 
procedure register_column
(p_appl_short_name in varchar2, –Custom Application Short Name
p_table_name in varchar2, –Table Name
p_column_name in varchar2, –Column Name
p_column_seq in number, –Sequence number of Column in table
p_column_type in varchar2, –Column Type (’NUMBER’, ’VARCHAR2’, ’DATE’, etc.).
p_column_width in number, –Colum Size,Can use 9 for DATE columns, 38 for NUMBER columns
p_nullable in varchar2, –Use ’N’ if the column is mandatory or ’Y’ if the column allows null values
p_translate in varchar2, –’N’ if the values are not translated (most application columns)
p_precision in number default null, –Number of digits in a number
p_scale in number default null); –Number of digits to the right of the decimal point in a number

Example

create table XXTEST_TEST_TABLE
(
unique_id number,
NAme varchar2(100),
Creation_Date date,
created_by number
)

API for register table 
Begin
ad_dd.REGISTER_TABLE('XXTEST','XXTEST_TEST_TABLE','T',1,10,0);
End;

Commit;

API for register column

EXECUTE ad_dd.REGISTER_COLUMN('XXTEST','XXTEST_TEST_TABLE','UNIQUE_ID',1,'NUMBER',38,'Y','N');

EXECUTE ad_dd.REGISTER_COLUMN('XXTEST','XXTEST_TEST_TABLE','NAME',2,'VARCHAR2',20,'Y','N')

EXECUTE ad_dd.REGISTER_COLUMN('XXTEST','XXTEST_TEST_TABLE','CREATION_DATE',3,'DATE',20,'Y','N')

EXECUTE ad_dd.REGISTER_COLUMN('XXTEST','XXTEST_TEST_TABLE','CREATED_BY',4,'NUMBER',38,'Y','N')

Commit;

Register Primary key in oracle application

execute ad_dd.register_primary_key('XXTEST', 'UNIQE_ID_PK', 'XXTEST_TEST_TABLE','UNIQE_ID_PK', 'S', 'Y','Y');

Commit;

Register Primary key column in oracle application

execute ad_dd.register_primary_key_column('XXTEST', 'UNIQE_ID_PK','XXTEST_TEST_TABLE', 'UNIQUE_ID', 1);

Commit;

How to check table registered in oracle application

Goto-->

Application Developer--> application-->Database-->Table

Search Table by Table name and detail will be shown

API for De-register table from oracle apps

EXECUTE ad_dd.DELETE_TABLE('BTVL', 'BTVL_TEST_TABLE');

COMMIT;

DROP TABLE BTVL_TEST_TABLE

API for initialize environment from backend in oracle R12

Pass parameter value user_id, resp_id and resp_appl_id

Begin
fnd_global.APPS_INITIALIZE(fnd_profile.value('USER_ID'),fnd_profile.value('RESP_ID'),fnd_profile.value('RESP_APPL_ID'))
End;

Invoice Payment detail report Query R12

SELECT  aba.batch_name invoice_batch,
        aia.source invoice_source,
        aia.org_id,
        hou.name OU_Name,
        pca.checkrun_name,
        pca.check_date,
        pca.check_number document_number,
        (select user_name from fnd_user where user_id = pca.created_by) checkrun_createdby,
        pca.attribute5 void_rsn,
        pca.attribute7 utr_no,
        pca.attribute8 txn_type,
        pca.bank_Account_num remit_to,
        pca.bank_account_name,
        pca.void_date,
        aia.invoice_num,
        aia.batch_id,
        aia.invoice_id,
        aia.doc_sequence_value voucher_num,
        REPLACE(REPLACE(aia.description,CHR(10),''),CHR(13),'') description,
        aia.invoice_currency_code,
        aia.payment_currency_code,
        aia.invoice_date,
        aia.invoice_amount,
        aia.amount_paid,
        aia.gl_date,
        aipa.payment_num,
        aipa.amount payment_amount,
        aipa.accounting_date payment_date,
        REPLACE(REPLACE(asi.vendor_name,CHR(10),''),CHR(13),'') vendor_name,
        REPLACE(REPLACE(asi.vendor_name_alt,CHR(10),''),CHR(13),'') vendor_name_alt,
        asi.segment1 vendor_number,
        aia.pay_group_lookup_code pay_group,
        REPLACE(REPLACE(assa.vendor_site_code,CHR(10),''),CHR(13),'') vendor_site_code,
        REPLACE(REPLACE(assa.vendor_site_code_alt,CHR(10),''),CHR(13),'') vendor_site_code_alt,
        flv_vendortype.meaning vendor_type,
        flv_invoicetype.meaning invoice_type,
        flv_paytype.meaning payment_type,
        flv_paystatus.meaning payment_status,
        (SELECT gross_amount
                   FROM  AP_PAYMENT_SCHEDULES_ALL
                   WHERE INVOICE_ID  = aia.invoice_id
                   AND   PAYMENT_NUM = aipa.payment_num
                   AND   ROWNUM=1) schedule_amount,
         (SELECT due_Date
                   FROM  AP_PAYMENT_SCHEDULES_ALL
                   WHERE INVOICE_ID  = aia.invoice_id
                   AND   PAYMENT_NUM = aipa.payment_num
                   AND   ROWNUM=1) due_date,
        aia.accts_pay_code_combination_id,
        DECODE(pca.void_date,NULL,'Negotiable','Void') inv_status,
        pca.check_id
FROM    ap_checks_all pca,
        ap_invoice_payments_all aipa,
        ap_invoices_all aia,
        hr_operating_units hou,
        ap_suppliers asi,
        ap_supplier_sites_all assa,
        ap_batches_all aba,
        fnd_lookup_values flv_vendortype,
        fnd_lookup_values flv_invoicetype,
        fnd_lookup_values flv_paytype,
        fnd_lookup_values flv_paystatus
WHERE   1=1
AND     flv_paystatus.lookup_code       = aia.payment_status_flag
AND     flv_paytype.lookup_code         = pca.payment_type_flag
AND     flv_invoicetype.lookup_code     = aia.invoice_type_lookup_code
AND     flv_vendortype.lookup_code      = asi.vendor_type_lookup_code
AND     aba.batch_id(+)                 = aia.batch_id
AND     assa.org_id                     = aipa.org_id
AND     assa.vendor_site_id             = aia.vendor_site_id
AND     asi.vendor_id                   = aia.vendor_id
AND     aipa.org_id                     = hou.organization_id
AND     hou.set_of_books_id             = aipa.set_of_books_id
AND     aia.invoice_id                  = aipa.invoice_id
and     pca.org_id                      = aipa.org_id
AND     pca.check_id                    = aipa.check_id
AND     flv_invoicetype.language        = 'US'
AND     flv_vendortype.language         = 'US'
AND     flv_paytype.language            = 'US'
AND     flv_paystatus.language          = 'US'
AND     NVL(flv_paystatus.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_invoicetype.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_vendortype.end_date_active,SYSDATE+1) > SYSDATE
AND     NVL(flv_paytype.end_date_active,SYSDATE+1) > SYSDATE
AND     flv_invoicetype.lookup_type     = 'INVOICE TYPE'
AND     flv_vendortype.lookup_type      = 'VENDOR TYPE'
AND     flv_paytype.lookup_type         = 'PAYMENT TYPE'
AND     flv_paystatus.lookup_type       = 'INVOICE PAYMENT STATUS'
AND     aipa.set_of_books_id            = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND     TRUNC(NVL(aipa.accounting_date,SYSDATE))   BETWEEN NVL (TRUNC (TO_DATE (p_txndate_from,
                                                                                'YYYY/MM/DD HH24:MI:SS')),
                                                                TRUNC (NVL (aipa.accounting_date, SYSDATE)))
                                                   AND NVL (p_txndate_to,
                                                                TRUNC (NVL (aipa.accounting_date, SYSDATE)))
AND     asi.vendor_id                = NVL(p_vendorid,asi.vendor_id)
AND     aia.org_id                   = NVL(p_ou_id, aia.org_id)
AND     asi.vendor_type_lookup_code  = NVL(p_vendortype, asi.vendor_type_lookup_code)                                                            
ORDER BY invoice_num
        ,aipa.payment_num
        ,accounting_date;

How to make Reject comments mandatory in oracle workflow

Use Below Code

PROCEDURE btvl_reject_commnet_mandate (
      p_item_type   IN              VARCHAR2,
      p_item_key    IN              VARCHAR2,
      actid         IN              VARCHAR2,
      funmode       IN              VARCHAR2,
      resultout     OUT NOCOPY      VARCHAR2
   )
   IS
      v_response         VARCHAR2 (2000);
      l_user_comment     VARCHAR2 (2000);
      approval_comment   EXCEPTION;
   BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
      v_response :=
                wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
      l_user_comment :=
         wf_notification.getattrtext (wf_engine.context_nid,
                                      'COMMENTS'
                                     );

/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
      BEGIN
         IF (    funmode = 'RESPOND'
             AND v_response = 'REJECTED'
             AND l_user_comment IS NULL
            )
         THEN
            RAISE approval_comment;
         END IF;
      EXCEPTION
         WHEN approval_comment
         THEN
            raise_application_error
               (-20010,
                'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
               );
            RAISE;
      END;
   END;

After compiling above procedure then call this procedure to notification 

Query for user responsibility name

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       frt.RESPONSIBILITY_ID,
       furg.start_date             "Start Date",
       furg.end_date               "End Date",    
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('DEV_TEAM')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
   AND fa.application_short_name =  'WIP'
 ORDER BY frt.responsibility_name;