Query To Get The Number of Seconds or Minutes or Hours Between Two Given Dates

For the Seconds:-
-------------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------
SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-
SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Thanks
Amar Alam

Log Message Short Cut for Back End and Front End

/****************
Amar Alam
****************/
PROCEDURE display_log (p_message in varchar2)
is
BEGIN
fnd_file.put_line (fnd_file.log, p_message);
dbms_output.put_line(p_message);
END display_log;

Ex:
BEGIN
display_log('Oracle Applications Blog');
END;

Thanks
Amar Alam

Query to find BOM Components

/*********************************************************
*PURPOSE: Query to find BOM Components *
 *AUTHOR: Amar Alam *
**********************************************************/

SELECT msi.segment1 "Parent material coding",
       msi.description "Parent item description",
       msi1.segment1 "Sub item code",
       msi1.description "Sub item description"
  FROM mtl_system_items_b msi,
       bom_bill_of_materials bom,
       bom_inventory_components bic,
       mtl_system_items_b msi1
 WHERE     1 = 1
       AND msi.organization_id = 91
       AND msi.inventory_item_id = bom.assembly_item_id
       AND msi.organization_id = bom.organization_id
       AND bom.organization_id = 91
       AND bom.alternate_bom_designator IS NULL
       AND bom.bill_sequence_id = bic.bill_sequence_id
       AND bic.disable_date IS NULL
       AND bic.component_item_id = msi1.inventory_item_id
       AND msi1.organization_id = 91
       AND msi.segment1 IN ('00603141A48R')         -- Parent Item
--   AND msi1.segment1 in ('MD150') -- The child

Thanks
Amar Alam

Query to find Item Cost in Oracle Apps

/*********************************************************
*PURPOSE: Query to find Item Cost *
*AUTHOR: Amar Alam *
 **********************************************************/

SELECT msi.segment1 "ITEM_NAME",
       msi.inventory_item_id,
       cic.item_cost,
       mp.organization_code,
       mp.organization_id,
       cct.cost_type,
       cct.description,
       cic.tl_material,
       cic.tl_material_overhead,
       cic.material_cost,
       cic.material_overhead_cost,
       cic.tl_item_cost,
       cic.unburdened_cost,
       cic.burden_cost
  FROM cst_cost_types cct,
       cst_item_costs cic,
       mtl_system_items_b msi,
       mtl_parameters mp
 WHERE     cct.cost_type_id = cic.cost_type_id
       AND cic.inventory_item_id = msi.inventory_item_id
       AND cic.organization_id = msi.organization_id
       AND msi.organization_id = mp.organization_id
       AND msi.inventory_item_id = 5014
       AND mp.organization_id = 93
       AND cct.cost_type IN  ('Frozen','Pending','C16')

Thanks
Amar Alam

O2C Technical Flow in Oracle Apps

SELECT DISTINCT
                OOHA.ORDER_NUMBER,
                OTTT.NAME "ORDER TYPE",
                OOHA.FLOW_STATUS_CODE HEADER_STATUS,
                OOLA.CUST_PO_NUMBER,
                OOLA.ORDERED_ITEM,
                OOLA.UNIT_SELLING_PRICE,
                ORDERED_QUANTITY,
                QLP.NAME  PRICE_LIST,
                (SELECT LAST_NAME||', '||FIRST_NAME FROM PER_ALL_PEOPLE_F WHERE PERSON_ID=RSA.PERSON_ID) SALESREP,
                OOD.ORGANIZATION_NAME,
                HCA.ACCOUNT_NAME "CUSTOMER NAME",
                HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
                HCSUA.LOCATION SHIP_TO_LOCATION,
                HL.ADDRESS1||','||HL.ADDRESS2||','||HL.ADDRESS3||','||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.POSTAL_CODE||','||HL.COUNTRY SHIP_TO_ADDRESS,
                HCSUA1.LOCATION BILL_TO_LOCATION,
                HL1.ADDRESS1||','||HL1.ADDRESS2||','||HL1.ADDRESS3||','||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.POSTAL_CODE||','||HL1.COUNTRY BILL_TO_ADDRESS,
                --WDD.DELIVERY_DETAIL_ID,
                WND.NAME "DELIVERY NUMBER"
                ,RCTA.TRX_NUMBER "INVOICE NUMBER"
                ,XTE.TRANSACTION_NUMBER,
                APSA.PAYMENT_SCHEDULE_ID,
                XLAE.ENTITY_ID,
                XLAH.AE_HEADER_ID,
                XLAL.GL_SL_LINK_ID,
                GIR.JE_BATCH_ID,
                L.JE_HEADER_ID,
                L.JE_LINE_NUM,
                B.NAME BATCH_NAME,
                B.DESCRIPTION BATCH_DESCRIPTION,
                H.NAME JOURNAL_NAME,
                H.STATUS JOURNAL_STATUS,
                H.DESCRIPTION JE_DESCRIPTION
FROM OE_ORDER_HEADERS_ALL OOHA,
                OE_ORDER_LINES_ALL OOLA,
                WSH_DELIVERY_DETAILS WDD,
                WSH_DELIVERY_ASSIGNMENTS WDA,
                WSH_NEW_DELIVERIES WND,
                OE_TRANSACTION_TYPES_TL OTTT,
                QP_LIST_HEADERS QLP,
                RA_SALESREPS_ALL RSA,
                RA_CUSTOMER_TRX_LINES_ALL RCTLA,
                RA_CUSTOMER_TRX_ALL RCTA,
                HZ_CUST_ACCOUNTS HCA,
                HZ_CUST_SITE_USES_ALL HCSUA,
                HZ_CUST_ACCT_SITES_ALL HCASA,
                HZ_PARTY_SITES  HPS,
                HZ_LOCATIONS HL,
                HZ_CUST_SITE_USES_ALL HCSUA1,
                HZ_CUST_ACCT_SITES_ALL HCASA1,
                HZ_PARTY_SITES  HPS1,
                HZ_LOCATIONS HL1,
                ORG_ORGANIZATION_DEFINITIONS OOD
                --AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
                --AR_CASH_RECEIPTS_ALL ACRA
                ,AR_PAYMENT_SCHEDULES_ALL APSA
                ,XLA.XLA_TRANSACTION_ENTITIES XTE
                ,XLA_EVENTS XLAE
                ,XLA_AE_HEADERS XLAH
                ,XLA_AE_LINES XLAL
                ,GL_IMPORT_REFERENCES GIR
                ,GL_JE_LINES L
                ,GL_JE_HEADERS H
                ,GL_JE_BATCHES B
WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
                AND OOHA.ORDER_NUMBER = :P_ORDER_NUMBER
                AND OOHA.HEADER_ID = WDD.SOURCE_HEADER_ID
                AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
                AND WDA.DELIVERY_ID = WND.DELIVERY_ID
                AND OOHA.PRICE_LIST_ID=QLP.LIST_HEADER_ID
                AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
                AND OOHA.SALESREP_ID=RSA.SALESREP_ID
                AND OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
                AND HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
                AND HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
                AND HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
                AND HCSUA.SITE_USE_CODE='SHIP_TO'
                AND HCASA.PARTY_SITE_ID=HPS.PARTY_SITE_ID
                AND HL.LOCATION_ID=HPS.LOCATION_ID
                AND HCSUA1.SITE_USE_ID=OOHA.INVOICE_TO_ORG_ID
                AND HCSUA1.CUST_ACCT_SITE_ID=HCASA1.CUST_ACCT_SITE_ID
                AND HCSUA1.SITE_USE_CODE='BILL_TO'
                AND HCASA1.PARTY_SITE_ID=HPS1.PARTY_SITE_ID
                AND HL1.LOCATION_ID=HPS1.LOCATION_ID
                AND OOHA.SHIP_FROM_ORG_ID = OOD.ORGANIZATION_ID
                AND TO_CHAR (OOHA.ORDER_NUMBER) = RCTLA.INTERFACE_LINE_ATTRIBUTE1
                AND TO_CHAR(WND.NAME)=RCTLA.INTERFACE_LINE_ATTRIBUTE3
                AND RCTLA.INVENTORY_ITEM_ID=OOLA.INVENTORY_ITEM_ID
                AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
                --AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
                --AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
                AND RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
                AND RCTA.TRX_NUMBER = XTE.TRANSACTION_NUMBER
                AND XLAE.ENTITY_ID = XTE.ENTITY_ID
                AND XLAE.APPLICATION_ID = XTE.APPLICATION_ID
                AND XLAH.EVENT_ID = XLAE.EVENT_ID
                AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
                AND GIR.GL_SL_LINK_TABLE = XLAL.GL_SL_LINK_TABLE
                AND GIR.GL_SL_LINK_ID = XLAL.GL_SL_LINK_ID
                AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
                AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
                AND H.JE_HEADER_ID = L.JE_HEADER_ID
                AND B.JE_BATCH_ID = H.JE_BATCH_ID
                AND H.JE_SOURCE = 'Receivables'
                AND H.STATUS = 'P';

Thanks
Amar Alam

Query To Get Form Personalizations Details

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

Query to Find Requisition Number from Purchase Order Number

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

Query to find Request Group for concurrent program

 SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = 'IFFCO FIN : Customer SOA Open Invoices - Malaysia';

Ur's
Amar Alam

PL/SQL - Date & Time

PL/SQL provides two classes of date and time related data types:

• Datetime data types
• Interval data types

The Datetime data types are:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The Interval data types are:

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

Both datetime and interval data types consist of fields. The values of these fields determine the value of the datatype. The following table lists the fields and their possible values for datetimes and intervals.

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any nonzero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds The 9(n) portion is not applicable for DATE.	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes) Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59 Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Not applicable for DATE or TIMESTAMP.	Not applicable

The Datetime Data Types and Functions

Following are the Datetime data types:

• DATE - it stores date and time information in both character and number datatypes. It is made of information on century, year, month, date, hour, minute, and second. It is specified as:
• TIMESTAMP - it is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, along with hour, minute, and second values. It is useful for storing precise time values.
• TIMESTAMP WITH TIME ZONE - it is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC. This datatype is useful for collecting and evaluating date information across geographic regions.
• TIMESTAMP WITH LOCAL TIME ZONE - it is another variant of TIMESTAMP that includes a time zone offset in its value.

Following table provides the Datetime functions (where, x has datetime value):

S.N	Function Name & Description
1	ADD_MONTHS(x, y); Adds y months to x.
2	LAST_DAY(x); Returns the last day of the month.
3	MONTHS_BETWEEN(x, y); Returns the number of months between x and y.
4	NEXT_DAY(x, day); Returns the datetime of the next day after x.
5	NEW_TIME; Returns the time/day value from a time zone specified by the user.
6	ROUND(x [, unit]); Rounds x;
7	SYSDATE(); Returns the current datetime.
8	TRUNC(x [, unit]); Truncates x.

Timestamp functions (where, x has a timestamp value):

S.N	Function Name & Description
1	CURRENT_TIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone.
2	EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x;
3	FROM_TZ(x, time_zone); Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Returns a TIMESTAMP containing the local time in the session time zone.
5	SYSTIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone.
6	SYS_EXTRACT_UTC(x); Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
7	TO_TIMESTAMP(x, [format]); Converts the string x to a TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Converts the string x to a TIMESTAMP WITH TIMEZONE.

Examples:

The following code snippets illustrate the use of the above functions:

SELECT SYSDATE FROM DUAL;

Output:

08/31/2012 5:25:34 PM

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output:

31-08-2012 05:26:14

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output:

01/31/2013 5:26:31 PM

SELECT LOCALTIMESTAMP FROM DUAL;

Output:

8/31/2012 5:26:55.347000 PM

The Interval Data Types and Functions

Following are the Interval data types:

• INTERVAL YEAR TO MONTH - it stores a period of time using the YEAR and MONTH datetime fields.
• INTERVAL DAY TO SECOND - it stores a period of time in terms of days, hours, minutes, and seconds.

Interval functions:

S.N	Function Name & Description
1	NUMTODSINTERVAL(x, interval_unit); Converts the number x to an INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Converts the number x to an INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Converts the string x to an INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Converts the string x to an INTERVAL YEAR TO MONTH.

Ur's
Amar Alam