Friday 15 April 2016

Query To Get Form Personalizations Details

12 comments
1)  SELECT FORM_ID, FORM_NAME, USER_FORM_NAME, DESCRIPTION
     FROM FND_FORM_VL
     WHERE FORM_NAME IN (SELECT FORM_NAME FROM FND_FORM_CUSTOM_RULES      GROUP BY FORM_NAME)

2) Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customization's. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.
FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule
FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the Easy Form form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the Audit Trail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
    A.Id,
    A.Form_Name ,
    A.Enabled,
    C.User_Form_Name,
    D.Application_Name ,
    A.Description,
    Ca.Action_Type,
    Ca.Enabled,
    Ca.Object_Type,
    ca.message_type,
    ca.message_text
from
    FND_FORM_CUSTOM_RULES a,
    FND_FORM b,
    FND_FORM_TL c,
    Fnd_Application_Tl D,
    Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
    And B.Form_Id = C.Form_Id
    And B.Application_Id = D.Application_Id
    And D.Application_Id = 230 --For Order Management
    And C.User_Form_Name Like 'Inventory%'  --All the Forms that Start with Sales
    And A.Enabled ='Y'
    and a.id = ca.rule_id


SELECT
    ffv.form_id          "Form ID",
    ffv.form_name        "Form Name",
    ffv.user_form_name   "User Form Name",
    ffv.description      "Form Description",
    ffcr.sequence        "Sequence",
    ffcr.description     "Personalization Rule Name"
FROM fnd_form_vl             ffv,
       fnd_form_custom_rules   ffcr
WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name, ffcr.sequence;



SELECT
    ffcr.SEQUENCE "Seq", ffcr.description "Description",
    DECODE (ffcr.rule_type,
           'F', 'Form',
            'A', 'Function',
            'Other'
           ) "Level",
    ffcr.enabled "Enabled",
    ffcr.trigger_event "Trigger Event",
    ffcr.trigger_object "Trigger Object",
    ffcr.condition "Condition",
    DECODE (ffcr.fire_in_enter_query,
            'Y', 'Both',
            'N', 'Not in Enter-Query Mode',
            'O', 'Only in Enter-Query Mode',
            'Other'
           ) "Processing Mode"
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = 'PO_POXPOEPO'
    AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;


Thanks
Amar Alam

Tuesday 12 April 2016

Query to Find Requisition Number from Purchase Order Number

15 comments
Query to find the purchase order number from requisition number:

SELECT DISTINCT pha.segment1
FROM po_headers_all pha,po_distributions_all pda,po_req_distributions_all rda,
po_requisition_headers_all rha,po_requisition_lines_all rla
WHERE 1=1
AND pha.po_header_id=pda.po_header_id
AND pda.req_distribution_id=rda.distribution_id
AND rda.requisition_line_id=rla.requisition_line_id
AND rla.requisition_header_id=rha.requisition_header_id
AND rha.segment1=:P_REQ_NUM

Query to find the requisition number from purchase order number:

SELECT DISTINCT rha.segment1
FROM po_requisition_headers_all rha,po_requisition_lines_all rla,
po_req_distributions_all rda,po_distributions_all pda,po_headers_all pha
WHERE 1=1
AND rha.requisition_header_id=rla.requisition_header_id
AND rla.requisition_line_id=rda.requisition_line_id
AND rda.distribution_id=pda.req_distribution_id
AND pda.po_header_id=pha.po_header_id
AND pha.segment1=:P_PO_NUMBER

Thanks
Amar Alam