Scripts required for 12.2.X custom table development

Scripts required for 12.2.X custom table development

1) For new table creations - after creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.

exec AD_ZD_TABLE.UPGRADE('XXCUST','XX_CUST_TBL1');

2) For table alteratuons - after running the DDL run below script to regenerate the editioning view for syncing any table changes.

exec AD_ZD_TABLE.PATCH('XXCUST','XX_CUST_TBL1')

3) Table grants must be done through API to avoid invalids.

exec AD_ZD.GRANT_PRIVS('SELECT','XX_CUST_TBL1','XX_ROLE0')

4) To view objects in all editions..add _ae to the data dictionary views

select * from user_objects_ae;

5) To update seed data tables in Patch edition, execute prepare command.

example:
 exec ad_zd_seed.prepare('WF_MESSAGES');

The prepare will create a edition based storage for the run edition data for which the updates will be made. During cutover, the run time data will be synced using forward/reverse cross edition triggers.

Service Contract Creation Technical Process

Mandatory setups for creating a contract

1. Customer should exist with bill to info

2.  Sales person should exit

3.  Price list should be defined

4.  Invoice and Payment terms should be defined

5.  Item should be defined and configured as service item

6.  QA check process

Technical Components Service Contract Update Process:

1.  Create CSV file with required columns (Work with user and get the required columns which business users thinks to update)

2.  After finalizing all the required columns create a staging table in Custom schema

3   Provide grant access to the staging table

4.  Make a note to provide custom table access in PROD at user level or developer group level

5.  Work with functional person and get the sample data in to CSV file

6.   Create a control file and load the data in to staging table

7.   Create a package which involves below components

a.     Create a function/procedure to create unique batch Id for every load.

b.     Create a function which Terminates at contract sub-line level, contract line level and     complete contract

c.    Create a function which can delete a contract

d.     Create a main procedure which can have complete validations and below API calls

                                                               i  .      Header API

                                                               ii .      Lines API

                                                               iii.      Sublines API

                                                               iv.      Billing API

                                                               v .      And Can use for calling all the functions and procedure according to the requirement which was created

e.   Create a function which can perform QA check

f.    Create a procedure to delete staging table data which has batch_id, contract number and creation date as parameters

g.   Create a procedure write_log for printing log messages in fnd_log_messages.

8.   Create concurrent programs

a.   Request Set

                                                               i.      EXT Service Contract Request set

b.  Concurrent Programs

                                                               i  .      EXT Upload for Service Contracts Creation

                                                               ii .      EXT Get Batch Id for Service Contracts

                                                               iii.      EXT Process Service Contracts Creation

                                                               iv.      EXT Delete Staging Table Data for Update Service Contract

9.   Once all the above code development is done make sure to do some negative testing to avoid errors and rework.

Scheduling a concurrent program in Oracle apps.

Scheduling a concurrent program in oracle apps:

In my previous article you have seen how to schedule a concurrent program from back end, now let us see how to schedule a concurrent program through oracle application.

You might be having couple of requirement where you want to run the report daily, weekly, monthly or on specific day at specific time.
For example: In finance company they want to run GL daily rate report every day midnight at 01:00 AM or other reports like 'Invoice hold report' run on specific days at specific time so every time submitting concurrent program is not possible manually. So Oracle introduced scheduling feature where users can schedule concurrent program as per their need.

Before we step in how to schedule a concurrent program, let us see what are the different scheduling features we have,

1. Once
2. Periodical
3. On specific day
4. Advanced (majorly not used)

Let us see how to schedule a concurrent program for ONCE:
Nav: Go to desired responsibility --> View --> request click on submit request and click on single request




 Now enter the concurrent program name, parameters and click on schedule button

 under run the job.. click on Once and enter the run time as per your requirement 



Now click on submit button



In the below screen shot you can see the program status has scheduled and phase as pending. on specific time it will change the status to running.



 Now on time the program has scheduled


Now the program completed sucessfully

•  Periodical Schedule:

 Now under run the job.. click on 'Periodically' and enter the run time as per your requirement



 Now click on ok and submit the request




 Now on time the program has scheduled
 



 As soon as it completes the program a new program has schedule after a minute
 


Now on time the program has scheduled
 

 you can see both the programs had completed successfully

• On Specific Days Schedule:

Now under run the job.. click on 'On Specific Days' and enter the run time as per your requirement.
But for the below request it run on 3rd, 5th and last day of the month.
 
 
click on Ok and submit the request.


Program has submitted as per the scheduled time.
 

 Now the next program has scheduled with status as scheduled to check the schedule time click on 'schedule'

 if you see the requested start date next schedule is on 5th.


In these way we can schedule the concurrent programs according to your requirements.