BI/DW Knowledge Space

Tuesday, February 8, 2022

Date Exp

Select * from tab where tname like '%%';

In Advanced-->Covert this filter to Sql in Create/Edit Filter

date '2021-07-02'

For Service dates since 90 days ="Invoice Detail"."Service Date" Between VALUEOF(V_CURR_DATE) and

TimeStampAdd(SQL_TSI_DAY, VALUEOF(V_CURR_DATE) , 90)

========ls===================================================================================================================

Select To_sdate(To_char(Sysdate,'MM-DD-YYYY'),'MM-DD-YYYY') as CurDate,

to_char(Sysdate,'Month') as Month,

to_char(Sysdate,'YYYY') as Year from dual

retrieve the Oracle version information: SELECT * FROM v$version; OR SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

Select To_date(To_char(Sysdate-1,'MM-DD-YYYY'),'MM-DD-YYYY') as PrevDate,

TO_CHAR(add_months(sysdate,-1),'MONTH') as PrevMonth,

to_char(ADD_MONTHS(Sysdate,-12),'YYYY') as PrevYear from dual

select to_char(trunc(sysdate,'MM'),'mm/dd/yyyy')from dual-----Start Date of the Month

select trunc(add_months(sysdate,0),'MM') from dual-----Start Date of the Month

SELECT LAST_DAY(sysdate) FROM dual--------Last Day of the Month

select to_char((next_day(sysdate, 'SAT')-6)-7) from dual----Last week First Day(Sunday)

select to_char(next_day(sysdate, 'SAT')-7) from dual------Last week Last Day(Saturday)

select to_char(next_day(sysdate, 'SAT')-6) from dual---start of the Current week(Sunday)

select to_char(next_day(sysdate, 'SAT')) from dual----Last Day of the Current week(Saturday)

select to_char(next_day(sysdate, 'SAT')-6)|| ' to ' ||to_char(next_day(sysdate, 'SAT')) this_week from dual----Current week dates between start date and end date

select to_char((next_day(sysdate, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(sysdate, 'SAT')-7) last_week from dual----Last week dates between start date and end date

select trunc(add_months(sysdate,-1),'MM') from dual-----First Day of Last Month

select LAST_DAY(add_months(sysdate,-1)) from dual-------Last Day of Last Month

select EXTRACT(MONTH from add_months(sysdate,-1) ) from dual----Last Month Digit(Num)

select TO_CHAR(add_months(sysdate,-1),'MONTH') from dual----Last Month Name

select add_months(sysdate,-1) from dual----Last month same date

select to_char(trunc(sysdate,'MM')+14,'mm/dd/yyyy') day from dual;--------to find 15th day of the month

select to_char(next_day(sysdate, 'SAT')-5) from dual

select to_char(next_day(sysdate, 'FRI')) from dual

============================================================================================================================

V_CURR_DATE_INIT

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY ,

DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))s

"Invoice Detail"."Service Date" Between VALUEOF(IstDayOfLstMon)

and TimeStampAdd(SQL_TSI_DAY, VALUEOF(LstDayOfLstMon) ,'MM')

For Oc Msg Report: Service Date between 08/01/2010 and 08/31/2010

"Invoice Detail"."Service Date" Between VALUEOF(IstDayOfLstMon)

and VALUEOF(LstDayOfLstMon)

"Flight Leg"."Date Of Operation"

----------------------------------------------------------------------------

ops$tkyte%ORA10GR2> select the_date,

2 to_char(next_day(the_date, 'SAT'))|| ' to ' ||to_char(next_day(the_date, 'SAT')-6)

this_week,

3 to_char((next_day(the_date, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(the_date,

'SAT')-7) last_week,

4 to_char(next_day(the_date, 'SAT')+7)|| ' to ' ||to_char(next_day(the_date, 'SAT'))

next_week

5 from (select sysdate+rownum-1 the_date from all_users where rownum <= 7)

6 /

ops$tkyte%ORA10GR2> select the_date,

2 begin_this_week-7 begin_prior_week, end_this_week-7 end_prior_week,

3 begin_this_week, end_this_week,

4 begin_this_week+7 begin_next_week, end_this_week+7 end_next_week

5 from

6 (

7 select the_date,

8 next_day(the_date-7,'mon') begin_this_week, next_day(the_date-2,'sat')

end_this_week

9 from (select sysdate+rownum-1 the_date from all_users )

select week_number,lag(my_date,1) over ( order by my_date ) Start_week ,my_date end_week from

(select trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') +r My_Date,

to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r,'YYYY') || ' Week# ' ||

to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r-1,'IW') Week_Number

from ( select (rownum-1)*7 r

from all_objects

)) W

---------------------------------------------

----Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

LastDay_PreviousMonth

----Last Day of Current Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

LastDay_CurrentMonth

----Last Day of Next Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

LastDay_NextMonth

First Day of Year: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Quarter: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE )

First Day of Month: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )

Query For Current Year and Quarter and Month

SELECT

TRUNC(cast(TO_CHAR(SYSDATE,'YYYY') as VARCHAR(20))),

TRUNC(CAST(TO_CHAR(SYSDATE,'Q') AS VARCHAR(20))),

TRUNC(CAST(TO_CHAR(SYSDATE,'MM') AS VARCHAR(20)))

FROM DUAL;

=================================================================

select 'Q4' qtr,trunc(sysdate,'Q') start_date,add_months(trunc(sysdate,'Q'),3) - 1 end_date from dual

union

select 'Q3',add_months(trunc(sysdate,'Q'),-3),trunc(sysdate,'Q') - 1 from dual

union

select 'Q2',add_months(trunc(sysdate,'Q'),-6),add_months(trunc(sysdate,'Q'),-3) - 1 from dual

union

select 'Q1',add_months(trunc(sysdate,'Q'),-9),add_months(trunc(sysdate,'Q'),-6) - 1 from dual

order by 1 desc

select add_months(trunc(sysdate,'Q'),-9) from dual-----Q1

select add_months(trunc(sysdate,'Q'),-6) from dual-----Q2

select add_months(trunc(sysdate,'Q'),-3) from dual-----Q3

select trunc(sysdate,'Q') from dual-----------------------------------Q4

select add_months(trunc(sysdate,'Q')+4,-9) from dual-----5th day of Q1

select add_months(trunc(sysdate,'Q')+4,-6) from dual-----5th day of Q2

select add_months(trunc(sysdate,'Q')+4,-3) from dual-----5th day of Q3

select trunc(sysdate,'Q')+4 from dual-----------------------------------5th day of Q4

select add_months(trunc(sysdate,'Q')+4,-9),add_months(trunc(sysdate,'Q')+4,-6),

add_months(trunc(sysdate,'Q')+4,-3),trunc(sysdate,'Q')+4 from dual

VALUEOF("5thdayofQ1")

select * from esvprod2.inv_header

select * from esvprod2.Inv_Header_Pmnt_Req_Remittance

select * from esvprod2.Inv_Remittance

select * from esvprod2.Inv_Error

select * from esvprod2.Esv_Error_Code

select * from esvprod2.Inv_status

Alias_Esv_Error_Code.ESV_ERROR_CODE_ID = Alias_Inv_Error.ESV_ERROR_CODE_ID

Alias_Inv_Header.INV_HEADER_ID = Alias_Inv_Error.INV_HEADER_ID

Alias_Inv_Status.INV_STATUS_ID = Alias_Inv_Header.INV_STATUS_ID

Alias_Inv_Header_Pmnt_Req_Remittance.INV_REMITTANCE_ID = Alias_Inv_Remittance.INV_REMITTANCE_ID

Alias_Inv_Header.INV_HEADER_ID = Alias_Inv_Header_Pmnt_Req_Remittance.INV_HEADER_ID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

for triggering the 5thDay of EveryQtr

case when CURRENT_DATE = VALUEOF("5thdayofQ4") then "Invoice Detail"."Service Date" else 0 end

and in filter is not equal to / is not in 0

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

select trunc(DEP_DATETIME_ACTUAL_LOCAL) from esvprod2.ESV_DAILY_FLT_LEG --03-JUL-10

select to_char(DEP_DATETIME_ACTUAL_LOCAL,'HH24:MI') from esvprod2.ESV_DAILY_FLT_LEG --03-JUL-10

to_date('10-12-06','MM-DD-YY')

to_date('jan 2007','MON YYYY')

to_date('2007/05/31','YYYY/MM/DD')

to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')

to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')

to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')

to_date('022002','mmyyyy')

to_date('12319999','MMDDYYYY')

to_date(substr( collection_started,1,12),'DD-MON-YY HH24')

to_date('2004/10/14 21', 'yyyy/mm/dd hh24')

TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

TRUNC(TO_DATE(W_INSERT_DT,'DD-MM-YY HH24:MI:SS'))='18-08-14'

SELECT column_name FROM ALL_TAB_COLUMNS where COLUMN_NAME like '%WID%' AND table_Name = 'WC_IPM_CONTRACT_TRANSACTION_F';

SELECT column_name,table_Name FROM ALL_TAB_COLUMNS where COLUMN_NAME like '%RIGHT%' AND owner ='FIPMS'

select count(*),INTEGRATION_ID,DATASOURCE_NUM_ID from WC_CDW_ACTIVITY_F group by INTEGRATION_ID,DATASOURCE_NUM_ID having count(*)>1;

select * from all_objects where object_name like 'DP_%';

select * from user_db_links;

select * from all_objects Where OWNER='OLAP_11G' and object_type='TABLE';

select * from all_objects where OWNER='GCW12C_BIPLATFORM' and object_type='TABLE' and object_name like 'S_NQ_%';

select * from all_users where username like 'MTA%' order by created desc;

select * from all_objects where object_name like 'ALL%PRIV%'order by 2;

select * from ALL_TAB_PRIVS_MADE where GRANTEE ='MTA';

---============================================

Current Month: MON-YY format

concat(concat(UPPER(MONTHNAME(current_date)), '-'),SUBSTRING(cast(YEAR(current_date)as char) FROM 3 FOR 4))

First Day of the Previous Year:

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

First Day of the Current Year:

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This calculation returns the first day of the year by deducting one less than the total number of days in the year.

First Day of the Next Year:

TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.

First Day of the Previous Month:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.

First Day of the Current Month:

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

SELECT TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) FROM "Project - Cost"

This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.

First Day of the Next Month:

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.

Last Day of the Previous Month:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.

Last Day of Current Month:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

SELECT TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) FROM "Project - Cost"

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.

Last Day of the Next Month:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.

Last Day of Previous Year:

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.

Last Day of Current Year:

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.

Last Day of the Next Year:

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

First Day of Current Quarter:

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.

Last Day of Current Quarter:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.

Number of days between First Day of Year and Last Day of Current Month:

TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn't want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

select a.integration_id,a.effective_from_dt, a.effective_to_dt, a.* from w_employee_d a Where a.Integration_id like '%\_%' ESCAPE '\'

order by 1;

To Verify the User sessions:

select sid,username,to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss') logon_time from v$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE';

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;---Check the size of User or Schema in Oracle.

To enforce a complete refresh of the MV:

BEGIN

dbms_mview.refresh('WC_SALES_ORD_LINE_ATT_MV', method => 'C', atomic_refresh => FALSE);

END;

To verify the active reporting DB/session:

Select * from V$instance;

WITH Clause:

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following. From <https://oracle-base.com/articles/misc/with-clause>

-- Non-ANSI Syntax

SELECT e.ename AS employee_name,

dc.dept_count AS emp_dept_count

FROM emp e,

(SELECT deptno, COUNT(*) AS dept_count

FROM emp

GROUP BY deptno) dc

WHERE e.deptno = dc.deptno;

-- ANSI Syntax

SELECT e.ename AS employee_name,

dc.dept_count AS emp_dept_count

FROM emp e

JOIN (SELECT deptno, COUNT(*) AS dept_count

FROM emp

GROUP BY deptno) dc

ON e.deptno = dc.deptno;

Using a WITH clause this would look like the following.

-- Non-ANSI Syntax

WITH dept_count AS (

SELECT deptno, COUNT(*) AS dept_count

FROM emp

GROUP BY deptno)

SELECT e.ename AS employee_name,

dc.dept_count AS emp_dept_count

FROM emp e,

dept_count dc

WHERE e.deptno = dc.deptno;

-- ANSI Syntax

WITH dept_count AS (

SELECT deptno, COUNT(*) AS dept_count

FROM emp

GROUP BY deptno)

SELECT e.ename AS employee_name,

dc.dept_count AS emp_dept_count

FROM emp e

JOIN dept_count dc ON e.deptno = dc.deptno;

WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.

WITH

dept_costs AS (

SELECT dname, SUM(sal) dept_total

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY dname),

avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) avg

FROM dept_costs)

SELECT *

FROM dept_costs

WHERE dept_total > (SELECT avg FROM avg_cost)

ORDER BY dname;

select (select count(*) c from W_SALES_ORDER_LINE_F) W_SALES_ORDER_LINE_F,

(select count(*) c from WC_OPM_MATLXACT_F) WC_OPM_MATLXACT_F

from dual;

Monday, April 3, 2017

P2P and O2C: Procure to Pay and Order to Cash Cycles

Oracle has developed this ERP solution which truly covers these both cycles as well as many others. Oracle EBS comprises of the Standard Core Business Management applications like General Ledger, Payables, Receivables, Purchasing, Order Management, Inventory, Discrete Manufacturing, Process Manufacturing, HRMS and many more.

The application mentioned are so integrated that it handles the beginning to end of both Assets and Liabilities. When referring to Assets it’s referring to applications like Order Management and Receivables, and when referring to Liabilities it’s referring to Purchasing and Payables and both of these Assets and Liabilities are finally pushed and calculated in Oracle General Ledger.

The base or the heart of Oracle EBS is Oracle General Ledger. We can say GL an intrinsic.

Procure to Pay:
Procure to pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.
Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.
Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best quote.

Oracle Purchasing: You prepare a Purchase Order (PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that supplier
Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in Oracle Purchasing.

Oracle Inventory / Oracle Assets: It’s up to us whether we want to receive the goods at our head office or Inventory directly. In either case we move the received goods to different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle Assets at the time of Invoice creation in Oracle Payables.

Oracle General Ledger: Once we move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.

Oracle Payables: After this the supplier sends the invoice for the purchased goods and we Enter or Match the invoice against the PO from Oracle Purchasing in Oracle Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.

Oracle General Ledger: When we enter the invoice it means that we have created a Liability against that supplier and also we have recorded the expense incurred or asset purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.

Oracle Payables: pay the invoice and settle the Liability.

Oracle General Ledger: The liability is settled and our cash movement account is updated.

Oracle Cash Management: As we pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled, Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.

Oracle General Ledger: our cash at bank is updated with actual balance.
Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM): we start the manufacturing of our final product. Either OPM or ODM requests the different raw

materials from our inventory organizations and manufactures a finished good.

Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.

Now the final product is ready to be sold in the market and from here the O2C cycle starts.

Order to Cash Cycle:
Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Oracle Order Management: Customer places the order.
Oracle Order Management: You enter the customer order
Oracle Inventory: Check the available unit and the quantity ordered by the customer.
Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.
Oracle Receivables: The customer receives the product and you invoice the customer.
Oracle General Ledger: You record your revenue and receivables.
Oracle Receivables: The customer pays and you receive the cash/check.
Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank balance or Oracle General Ledger.
Oracle General Ledger: You have the actual bank balance.

This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them.

Tuesday, June 21, 2016

Sorting outer group based on inner group in BI Publisher

Consider the following dataset

<top>

<data_node> <text_field>d</text_field> <num_field>1</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>2</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>3</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>4</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>5</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>6</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>7</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>9</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>8</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>10</num_field> </data_node>

</top>

The requirement is to group the text_field data together and to order it based on num_field. In the above XML data source, text_field ‘d’ has the smallest number value i.e. 1. So, according to the requirement, D should be displayed on the top with all its num_fields i.e. (1,2,11). After D, C should be displayed since C has a num_field value of 2. C should again be followed by all its num_fields i.e. (2,8,10)

So the output should be

1,2,11

2,8,10

3,5,9

4,6,7

After a day of dealing with arrays, variables and tons of rearrangement, the following solution looked most elegant

<?for-each-group:data_node;./text_field?><?sort:current-group()/num_field;'ascending';data-type='number'?>

<?text_field?>

<?for-each@inlines:current-group()/num_field?><?sort:current-group()/num_field;'ascending';data-type='number'?><?.?>,

<?end for-each?>

<?end for-each-group?>

Magic is done by the for-each-group statement at the top. When the field on which grouping is done (text_field in our case) is different from the field on which sorting is done(num_field in our case), BI Publisher sorts the group-field based on the sort-field

The sort statement inside for-each:current-group() statement is to sort the num_field data inside each text_field group

Simple RTF Template report in BI Publisher

RTF- Rich Text Format is a document file format which is used for common word processing applications such as Microsoft Word. Word file is saved as a RTF just by giving ‘.rtf’ extension to the file name. This file format is very useful and easy to create reports for business scenario accomplished with oracle’s Business Intelligence Publisherknown as BI publisher.

This is a very powerful and efficient tool for creating business reports. There is tons of content to learn about BI publisher. We will start learning this one by one so let’s start with creating one simple report.

For that we require one XML file as input or to load into RTF. Consider a book.xml which is easily available on web and you can easily access it for practice. I paste some sample XML data from book.xml so that you can identify and use same XML.

Now we have to create report considering following one simple requirement.

Requirement:

Get the detail list of the books of ‘Price’<10.

Solution:

Now for the given XML there are 12 books. We have to filter out those books from the list which has their price less than 10. First of all we will list the total book-list then will try to apply this filter to get the requirement work. Now to get the list of total books we will proceed step by step.

Step #1: Create RTF file.

Open a blank word document. Save that word document with ‘.rtf’ extension. Lets give here ‘books.rtf’.

Step #2: Load the XML.

Now we have to load that Books.xml into created Books.rtf. To do this, click on the ‘Add-Ins’ from the title bar of word file. Following window will pop up.

Click on ‘Data’ and then ‘Load XML Data’. Refer below image.

Then you have to select the XML which you want load into RTF. If the XML load is successful then following window will pop up.

Step #3: Insert Table.

As the XML is loaded successfully next step we have to get the table form of this XML data. To do so navigate to ‘Insert’ tab which is next to ‘Data’. Refer above image. After that select Table wizard as shown in below image.

After selecting ‘Table Wizard’ following window will pop up.

Select ‘Table’, click on ‘Next’. You will see the hierarchies available in loaded XML. But for our XML there is only one so its showing that one as default. Refer below image. Click ‘Next’

Above image shows list of all the field names for particular Book ID tab. You can select which are necessary. In our case we have to get detailed list so will select all field names. Those which are selected will get shifted to the right pane as below.

If we click on ‘Next’, click on ‘Finish’. Following table will get loaded in your RTF pane.

You can adjust the column width, font, font size, column header etc. as per your requirement.

Step #4:After this table get inserted we have to check it’s giving correct output or not. To do so navigate through ‘Preview’ tab. You will find options like PDF, HTML etc. refer below image. You will get the idea.

These PDF. HTML, EXCEL etc. options gives facility to show output in different format. Let’s take our output in PDF format. Click on ‘PDF’. If your word document is not saved as RTF file it will ask you to save it in RTF format. Just save it using ‘.rtf’ extension. Otherwise it will show your PDF output as below.

Step #5: Work on requirement.

Now we have to get our requirement work. For that we have to apply some logic like “If price < 10 then show corresponding records”. Follow the following procedure. You will get the clear idea.

As we have to deal with ‘price’, double click on it, following window will pop up.

Click on ‘Advance’ Tab.

This symbol <? ?> gives the value of field whichever is inside it. As per now we are getting ‘price’ value because of it.

Now we have to apply our logic. To do so write following code or query in advance tab as shown in following image.

Now run the report by navigating through ‘Preview’ -> ‘PDF’. You will get detailed list of books of having price less than 10 as below. All the record of having price > 10 will get omitted.

I hope this will help you to create your BIP report. This is efficient and easy to understand method I followed when I started working on BIP. There are another method to put ‘If’ condition also but it needs some modification. We will see those in detail along with “Group by”, “Sort”, “Color formatting” etc. from next article.

Cross-Tab/Matrix Feature in XML Publisher

A matrix (crosstab) report contains one row of labels, one column of

labels, and information in a grid format that is related to the row and column labels. In other words, a matrix report displays the data in a grid. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.

A simple matrix report might look like the one below, where the jobs are fetched dynamically for the first row and the departments are fetched dynamically for the first column. The salaries of the employees with the job in a particular department are places along the grid. The aggregate totals are displayed in the last row and last column.

Dept	Analyst	Clerk	Manager	Total
10		1300	2450	3750
20	6000	1900	2975	10875
30		950	2850	3800
Total	6000	4150	8275	18425

Cross-Tabs are covered under advanced report layouts in XML Publisher. It would be helpful to first read the XMLP basics before continuing with cross-tabs.

XMLP Basics Reference Link: XMLP Basics

Cross-Tabs feature in XML Publisher is synonymous to Matrix reports in Oracle Reports 6i. I will first explain the example given in XML Publisher User Guide under the section "Creating RTF Template > Advanced Report layouts> Cross-Tab Support". The RTF template given in user guide doesn’t give the desirable results, so I will explain how the code should be modified to get the correct results.

XML Data File:The following is the xml data file used for our example. The xml data file contains the quarter sales results of different industries over a period of time. I have slightly modified the data such that there are no quarter sales results for “Home Furnishing” industry for the year 2003.