Billing Period Details as Shown on Sales Order Screen.sql

SELECT
	h.source_order_number Src_Order
  , h.order_number        Order_Num
  , h.source_order_id
  , h.status_code
  , l.DISPLAY_LINE_NUMBER                                  Lin
  , (f.SOURCE_LINE_NUMBER || '.' || f.fulfill_line_number) flin
  , f.fulfill_line_id
  , f.status_code flin_stat
  , f.ordered_qty Order_Q
  , f.shipped_qty SHIP_Q
  , bp.NET_BILLING_AMT_PER_PERIOD
  , NVL(bp.BILLING_PLAN_TYPE_CODE, (
		SELECT
			'Defaulted to: ' || Meaning
		FROM
			Fusion.FND_LOOKUPS
		WHERE
			Lookup_type     = 'ORA_DOO_BILLING_PLAN_TYPE_CODE'
			AND Lookup_Code = 'ONE TIME'
	)
	)                                                         Billing_Plan
  , NVL(TO_CHAR(bp.BILLING_NUM_OF_PERIODS),'Defaulted to: 1') Number_Periods
  , bp.PERIODICITY_CODE
  , bp.BILLING_PERIOD_START_DATE
  , bp.BILLING_PERIOD_END_DATE
FROM
	fusion.doo_fulfill_lines_all f
  , fusion.doo_lines_all         l
  , fusion.doo_headers_all       h
  , fusion.doo_billing_plans     bp
WHERE
	h.header_id = f.header_id
	--       AND h.source_order_number IN ('Enter_Order_Number_Here')
	AND h.header_id       = l.header_id
	AND l.line_id         = f.line_id
	AND f.fulfill_line_id = bp.fulfill_line_id (+)
	AND h.submitted_flag  = 'Y'
ORDER BY
	h.creation_date DESC
  , h.order_number
  , f.SOURCE_LINE_NUMBER
  , f.fulfill_line_number