Supplier Interface Staging table

Staging table structure of supplier Interface-----

create table xx_ap_suppliers_stg --ap_suppliers_int
(
vendor_full_name varchar2(240) not null --VENDOR_NAME
,vendor_code varchar2(30) not null --SEGMENT1
,alt_vendor_name varchar2(320) --VENDOR_NAME_ALT
,active_vendor_since date --START_DATE_ACTIVE
,inactive_date date --END_DATE_ACTIVE
,vendor_type varchar2(30) --VENDOR_TYPE_LOOKUP_CODE
,domestic_or_international varchar2(25) --STANDARD_INDUSTRY_CLASS
,customer_num varchar2(25) --CUSTOMER_NUM
,parent_vendor_code varchar2(30) --
,small_scale_industry varchar2(1) --SMALL_BUSINESS_FLAG
,industry varchar2(25) --ORGANIZATION_TYPE_LOOKUP_CODE
,min_order_amount number --MIN_ORDER_AMOUNT
,inspection_reqd_flag varchar2(1) --INSPECTION_REQUIRED_FLAG
,receipt_reqd_flag varchar2(1) --RECEIPT_REQUIRED_FLAG
,qty_receipt_tolerance number --QTY_RCV_TOLERANCE
,days_early_receipt_allwd number --DAYS_EARLY_RECEIPT_ALLOWED
,days_late_receipt_allwd number --DAYS_LATE_RECEIPT_ALLOWED
,substitute_receipt_allwd varchar2(1) --ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,unordered_receipts_allowed varchar2(1) --ALLOW_UNORDERED_RECEIPTS_FLAG
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

create table xx_ap_supplier_sites_stg --ap_supplier_sites_int
(
vendor_code varchar2(30) not null --for reference to supplier
,vendor_site_code varchar2(15) not null --VENDOR_SITE_CODE
,vendor_site_code_alt varchar2(320) --VENDOR_SITE_CODE_ALT
,operating_unit varchar2(30) not null --ORG_ID
,inactive_date date --INACTIVE_DATE
,purchasing_site_flag varchar2(1) --PURCHASING_SITE_FLAG
,rfq_only_site_flag varchar2(1) --RFQ_ONLY_SITE_FLAG
,pay_site_flag varchar2(1) --PAY_SITE_FLAG
,address_line1 varchar2(240) --ADDRESS_LINE1
,address_line2 varchar2(240) --ADDRESS_LINE2
,address_line3 varchar2(240) --ADDRESS_LINE3
,address_line4 varchar2(240) --ADDRESS_LINE4
,city varchar2(25) --CITY
,district varchar2(150) --PROVINCE
,state varchar2(150) --STATE
,pin varchar2(20) --ZIP
,country varchar2(25) --COUNTRY
,landmark varchar2(560) --ADDRESS_LINES_ALT
,area_code varchar2(10) --AREA_CODE
,phone varchar2(15) --PHONE
,fax varchar2(15) --FAX
,vendor_internet_site varchar2(2000) --EMAIL_ADDRESS
,freight_terms varchar2(25) --FREIGHT_TERMS_LOOKUP_CODE
,payment_terms varchar2(50) --TERMS_NAME
,terms_date_basis varchar2(25) --TERMS_DATE_BASIS
,pay_date_basis varchar2(25) --PAY_DATE_BASIS_LOOKUP_CODE
,pay_group varchar2(25) --PAY_GROUP_LOOKUP_CODE
,payment_priority number --PAYMENT_PRIORITY
,invioce_currency varchar2(15) --INVOICE_CURRENCY_CODE
,payment_currency varchar2(15) --PAYMENT_CURRENCY_CODE
,invoice_amount_limit number --INVOICE_AMOUNT_LIMIT
,vat_code varchar2(20) --VAT_CODE
,preferred_payment_method varchar2(25) --PAYMENT_METHOD_LOOKUP_CODE
,vendor_bank_name varchar2(50) --
,bank_acct_number varchar2(50) --
,hold_unmatched_invoices varchar2(1) --HOLD_UNMATCHED_INVOICES_FLAG
,vat_regd_num varchar2(20) --VAT_REGISTRATION_NUM
,bank_charge_bearer varchar2(1) --BANK_CHARGE_BEARER
,l_segment1 varchar2(10)
,l_segment2 varchar2(10)
,l_segment3 varchar2(10)
,l_segment4 varchar2(10)
,l_segment5 varchar2(10)
,l_segment6 varchar2(10)
,l_segment7 varchar2(10)
,l_segment8 varchar2(10)
,p_segment1 varchar2(10)
,p_segment2 varchar2(10)
,p_segment3 varchar2(10)
,p_segment4 varchar2(10)
,p_segment5 varchar2(10)
,p_segment6 varchar2(10)
,p_segment7 varchar2(10)
,p_segment8 varchar2(10)
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

create table xx_ap_sup_site_contacts_stg --ap_sup_site_contact_int
(
vendor_code varchar2(30) not null --reference to supplier
,vendor_site_code varchar2(15) not null --reference to site code
,prefix varchar2(5) --PREFIX
,first_name varchar2(15) --FIRST_NAME
,middle_name varchar2(15) --MIDDLE_NAME
,last_name varchar2(20) --LAST_NAME
,designation varchar2(30) --TITLE
,department varchar2(230) --DEPARTMENT
,email varchar2(2000) --EMAIL_ADDRESS
,area_code varchar2(10) --AREA_CODE
,phone varchar2(15) --PHONE
,alt_phone varchar2(15) --ALT_PHONE
,fax varchar2(15) --FAX
,inactive_date date --INACTIVE_DATE
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

DATE FUNCTION to Return a start time

FUNCTION get_oper_start_time(v_schedule_DATE DATE) RETURN DATE IS
BEGIN
RETURN to_DATE(to_char(v_schedule_DATE,'dd-mm-yyyy') || '08:00 AM', 'dd-mm-yyyy hh:mi AM');

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,'Exception (OTHERS) in get_oper_start_time '|| SQLERRM);
RETURN NULL;
END get_oper_start_time ;

Oracle Workflow Engine API

The Oracle Workflow Engine:

• Manages the state of activities for each process instance.

• Is implemented in server-side PL/SQL. Combination of Tables/Views/Packages and procedures

• Determines the next activity once a prerequisite activity completes.

• Executes function activities automatically.

• the Notification System to send notification messages

• Supports results-based branches, parallel branches, loops, and sub-processes.

• Can execute activities from non-savepoint environments such as database triggers and distributed transactions.

• Can defer activities too costly to execute in real time to background engines for processing.

• Maintains a history of completed activities.

• Detects error conditions and executes error processes.

Initiating a Workflow Process
We Call the Workflow Engine APIs to initiate a workflow process.
Use the CreateProcess and StartProcess APIs if you want to perform additional tasks, such as setting item attributes, after creating and before starting the process.
If you do not need to perform any additional tasks, you can use the LaunchProcess API, which is a wrapper combining the CreateProcess and StartProcess APIs.

The procedure that executes the Workflow Engine APIs to initiate a process must identify the item type and item key of the process for these APIs. The item type and item key passed to these APIs uniquely identify an item and must be passed to subsequent API calls for each specific process.


Workflow Engine Processing
Upon starting a process, the Workflow Engine:

• Identifies and executes the Start activity node

Executes the Start node if it is a function, notification, or process activity

• Determines the next activity to transition to after completing the prerequisite activity or activities

• Drives through the process

Automatically executes function activities and Send or Raise event activities
Pauses when it encounters a notification activity or blocking activity

• Calls the Notification System to notify a performer

Transitions to the next activity after the performer completes the notification,
the blocking activity is completed, or the event message is received

• when it encounters an End activity

Exception Handling
Use WF_CORE APIs to raise and catch errors in your PL/SQL procedures.

The Workflow Engine sets the status of the function activity to “ERROR” if:
The PL/SQL procedure raises an unhandled exception
The PL/SQL procedure returns a result beginning with “ERROR:”

If an activity encounters an error, information about the error is stored in the following columns in the WF_ITEM_ACTIVITY_STATUSES table, which are viewable from the Workflow Monitor.

• ERROR_NAME

• ERROR_MESSAGE

WORKFLOW ENGINE API’s
To start or run a workflow process

• WF_ENGINE.CreateProcess creates a new runtime process for a work item.

• WF_ENGINE.StartProcess begins execution of the specified process.

• WF_ENGINE.LaunchProcess launches a specified process by creating the new runtime process and beginning its execution.

• WF_ENGINE.SetItemOwner sets the owner of an existing item.

• WF_ENGINE.SetItemUserKey sets a user-friendly identifier for an item.

• WF_ENGINE.GetItemUserKey returns the user-friendly identifier assigned to an item.

• WF_ENGINE.SetItemParent defines the parent/child relationship for master/detail processes.

• WF_ENGINE.Event receives an event from the Business Event System into a workflow process.

• WF_ENGINE.Background runs a background engine to process deferred and timed out activities and stuck processes.

• WF_ENGINE.CreateForkProcess forks a runtime process by creating a new process that is a copy of the original.

• WF_ENGINE.StartForkProcess begins execution of the specified new forked process.

To communicate attribute information to the Workflow Engine

• WF_ENGINE.SetItemAttrText, WF_ENGINE.SetItemAttrNumber, WF_ENGINE.SetItemAttrDate, and WF_ENGINE.SetItemAttrEvent set the value of an item type attribute in a process.

• WF_ENGINE.SetItemAttrTextArray, WF_ENGINE.SetItemAttrNumberArray, and WF_ENGINE.SetItemAttrDateArray set the values of an array of item type attributes in a process.

• WF_ENGINE.GetItemAttrText, WF_ENGINE.GetItemAttrNumber, WF_ENGINE.GetItemAttrDate, and WF_ENGINE.GetItemAttrEvent return the value of an item type attribute in a process.

• WF_ENGINE.GetItemAttrInfo returns information about an item attribute, such as its type and format.

• WF_ENGINE.AddItemAttr adds a new item attribute to the runtime process.

• WF_ENGINE.AddItemAttrTextArray, WF_ENGINE.AddItemAttrNumberArray, and WF_ENGINE.AddItemAttrDateArray add an array of new item type attributes to the runtime process.

• WF_ENGINE.GetActivityAttrText, WF_ENGINE.GetActivityAttrNumber, WF_ENGINE.GetActivityAttrDate, and WF_ENGINE.GetActivityAttrEvent return the value of an activity attribute in a process.

• WF_ENGINE.GetActivityAttrInfo returns information about an activity attribute, such as its type and format.

To communicate state changes to the Workflow Engine

• WF_ENGINE.CompleteActivity notifies the engine that the specified activity has been completed for the item, identifying the activity by the activity node label name.

• WF_ENGINE.CompleteActivityInternalName notifies the engine that the specified activity has been completed for the item, identifying the activity by its internal name.

• WF_ENGINE.BeginActivity determines if the specified activity can currently be performed and raises an exception if it cannot.

• WF_ENGINE.AssignActivity assigns an activity to another performer.

• WF_ENGINE.GetActivityLabel returns the instance label of an activity, given the internal activity instance identification.

• WF_ENGINE.AbortProcess aborts process execution and cancels outstanding notifications.

• WF_ENGINE.SuspendProcess suspends process execution so that users cannot transition items to new activities.

• WF_ENGINE.ResumeProcess returns a suspended process to normal execution status.

• WF_ENGINE.HandleError handles any activity that has encountered an error. This API can also be called for any arbitrary activity in a process to roll back part of the process to that activity.

• WF_ENGINE.ItemStatus returns the status and results for the root process of the specified item instance.

FND_USER LOCK TABLE

The SQl will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.


SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

Concept of Key Flexfield and Descriptive Flexfield

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number a cost centre etc
Desc Flex is used to just capture extra information.
Key Flexfields have qualifiers whereas Desc Flexfields do not.
Desc Flexfields can have context sensitive segments while Key flexfields cannot.

To desing key flex field one must follow the below step....

• Identifying Key flexfields that are required for you Oracle Applications installation
• Designing a Key flexfield structure
• Selecting the appropriate Key flexfield to define
• Defining structure information for the Key flexfield
• Defining segment attributes
• Defining flexfield qualifiers
• Defining segment qualifiers
• Implementing optional features as needed

Lets say we are defining the structure for an Accounting Flexfield. Query for the Flexfield Title “Accounting Flexfield” using the Flexfield Title window will appper. If there are already existing structures for Accounting Flexfields, they would be listed in the Structures window .
Now
Lets say we want to define a new structure for the Accounting Flexfield with the structure code as “NEW_ACCOUNTING_STRUCTURE”. Enter the code name in the Code window and the title as “New Accounting Flexfield Struc” in the Title window. Enter an optional description for the new structure which may include the purpose of the new structure. Save the changes.

There are few check buttons on the form which can be left unchecked/checked.

The Enabled checkbox is checked by default. You can optionally uncheck it. You cannot delete a Key flexfield structure, but you can disable it by unchecking the Enabled checkbox. Next, you may select a Segment Separator other than the default period. Your choices are period, dash, pipe, or any custom character. You must have a segment separator.

Next, you can select whether you want to Cross-Validate Segments. Unchecking this checkbox will disable cross-validation for this Key flexfield structure. You can then identify whether you want to Freeze Rollup Groups. If you mark this checkbox, you will not be able to change the assigned rollup groups in the Define Segment Values form. As a last step, you can determine whether you want to Allow Dynamic Inserts for Key flexfield structures. If you do not allow dynamic inserts, you must create all flexfield combinations in the combination form.