Requisition Interface

Requisition Interface - PO_REQUISITIONS_INTERFACE_ALL 
-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation 
,line_attribute15 ---xtra infomation 
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation 
,rec_get_lines_info.ship_to_org_id ---xtra infomation 
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id 
, rec_get_lines_info.vendor_site_id 
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
); 


Note: 

Standard grouping rules provided by Oracle are by
Buyer 
Category
Item
Location
Vendor or 
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1

Concurrent Program: 

apps.fnd_request.submit_request (application => 'PO' --Application, 
program => 'REQIMPORT' --Program, 
argument1 => 'INV' --Interface Source code, 
argument2 => '' --Batch ID, 
argument3 => 'ALL'--Group By, 
argument4 => ''--Last Req Number, 
argument5 => ''--Multi Distributions, 
argument6 => 'N' --Initiate Approval after ReqImport 
);

Purchase Order Interface

Purchase Order Interface -- PO_HEADERS_INTERFACE 
Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information 
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information 
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id 
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory 
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory 
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program: 
apps.fnd_request.submit_request (application => 'PO' --Application, 
program => 'POXPOPDOI'--Program, 
argument1 => ''--Buyer ID, 
argument2 => 'STANDARD'--Document Type, 
argument3 => ''--Document Subtype, 
argument4 => 'N'--Process Items Flag, 
argument5 => 'N'--Create Sourcing rule, 
argument6 => ''--Approval Status, 
argument7 => ''--Release Generation Method, 
argument8 => ''--NULL, 
argument9 => g_org_id--Operating Unit ID, 
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

setting the Password for PDF File sent through XML Publisher

setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File - > Properties 
Create a new custom property 
a) Name : xdo-pdf-open-password
Type : text 
Value : Either Hard Code the value / or get the value for xml data 
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .

What Is Dynamic SQL?

What Is Dynamic SQL?
Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

In R12 what is MO_GLOBAL.INIT

Purpose of mo_global.init :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp 

When & from where is mo_global.init called ?

This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

From SQL*Plus, you can simulate login to a specific responsibility by calling
a.Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc

b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Best Practices in Report Development

The Information drawn from some of standard documents and Development Experience.
These Tips are helpful to the Developers to maintain standards during OracleApplication Reports Development. 

Before Starting Report:
The following points to be considered before starting report development:
Mode: Report Development can be done by two modes
i. Character Mode
ii. Bit Mapped.

To Set the Character Mode: Yes in Reports Property
If it is Bitmapped report then the above should be NO.
In System Parameter the Mode Parameter, initial value should be made as character orBitmapped.

Report Style
Portrait: if it is Portrait then the size would be like (80 * 66)
Landscape: 132 * 66
Land wide: 180 * 66.

The initial value should be set at system parameters ‘Orientation’.

Using Triggers in Reports:
There are 5 main triggers in Report and they can be used for different purposesas per the sequence of fire we have to write logic on that :

Before Report Trigger
For initializing environment or using User Exits we can use this trigger
As SRW.USER_EXIT (‘FND SRWINIT’)
It will set the profile option values.It can also be used to select the organization automatically.

After Report Trigger
SRW.USER_EXIT (‘FND SRWEXIT’)
This will ensure that memory allocated for AOL user exit is freed up now.

Naming Conventions
Query and Group Names
The query and group name should be meaningful as :Q_Headers ,Q_Lines, G_Headers etc.
This practice will help us to recognizeThe contents or purpose of the query.

Fields, Repeating frames, Boiler Texts
In the layout fields name should be similar to database column names.And repeating frames must reflect the relevant groups. This is useful torecognize the field or boiler palette or tracking the error of layout.

Parameters
Bind Parameters Bind parameters should start by ‘P’
Lexical Parameters should start by ‘LP’.

Exception Handling
In Programming units and formula columns we should handle possible
Exceptions as ‘NO DATA FOUND’ (one of them).
In Numeric formula Columns always handle Zero Divide error and use NVL
As
Valmont := (:quantity * :rate) / :xyz
Here if :xyz is Zero then we will get Zero Divide Error.
We should implement above logic as :
-----> V_Amount := (NVL(:qty,0) * NVL(:rate,0) / NVL(:xyz,1);

Using %type
While defining the data types in program units using %type is better practice,
function F_E MPFormula return Number is
V_EMPNO EMP.EMPNO%TYPE
Instead of :-
function F_E MPFormula return Number is
V_EMPNO number (10); 

Debugging Reports
We can use srw.message to debug the report.
The output of it we can see at log file of that report.

No Data Found
For displaying NO DATA FOUND in the Report if the query is not returns anyrows. Make a text field “NO DATA FOUND” in the layout of the Report, Make asummary column that will give count of any not null field in the report , and in theformat trigger the logic should bre like if count >0 thenreturm true else return false .

END OF REPORT
To display END OF REPORT at the last line of the report just place an Text “END OFREPORT”, aligned center in the layout of the report.

How to set Org_id at back-end

How to set Org_id at back-end 
We have a API to set the org_id from the backend. 

begin 
fnd_client_info.set_org_context(); 
end; 

Note:- Org_id indicates the "Operating Unit" in the Business Structure. 
How to I know what Org_id is set at back-end 
We can use userenv function to know the Org_id set at backend. 
select userenv('CLIENT_INFO') from dual; 

Why we need to set org_id at back-end 
We usually, set org_id at back-end. So that we can get the org_id records from the Multi Organization views. 

Above is applicable only till 11i version. It is different in Release 12.