USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_get_full_filing
============================================================================
 Returns every non-dimensional, consolidated numeric fact from a single
 filing, across ALL five statement types (BS / IS / CF / CI / EQ),
 stacked into one result set and tagged with the statement it came from.

 Unlike usp_fsnds_get_statement which targets one statement and
 enforces qtrs/ddate rules, this SP is a "give me everything that
 ties to this filing" dump. Useful for:
   * sanity checks
   * bulk export of a single filing
   * building reports that need BS + IS + CF together

 Parameters
 ----------
   @adsh    accession number
   @iprx    default 0 (fsnds-local canonical precision)
   @inpth   default 0

 Shape
 -----
   stmt | plabel | tag | ddate | qtrs | uom | value | report | line
============================================================================
*/

IF OBJECT_ID('dbo.usp_sec_get_full_filing', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_sec_get_full_filing;
GO

CREATE PROCEDURE dbo.usp_sec_get_full_filing
    @adsh  VARCHAR(20),
    @iprx  INT = 0,
    @inpth INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @subID INT = (SELECT subID FROM dbo.sub WHERE adsh = @adsh);
    IF @subID IS NULL
    BEGIN
        RAISERROR('No sub row for adsh=%s', 16, 1, @adsh);
        RETURN;
    END;

    SELECT
         ls.stmt
        ,p.plabel
        ,t.tag
        ,n.ddate
        ,n.qtrs
        ,u.uom
        ,IIF(p.negating = 1, n.value * -1, n.value) AS value
        ,p.report
        ,p.line
    FROM dbo.pre p
    INNER JOIN dbo.lkp_stmt ls ON ls.stmtID = p.stmtID
    INNER JOIN dbo.num n
      ON  n.subID = p.subID
      AND n.tagID = p.tagID
    INNER JOIN dbo.tag      t ON t.tagID = n.tagID
    INNER JOIN dbo.lkp_uom  u ON u.uomID = n.uomID
    WHERE p.subID   = @subID
      AND p.inpth   = @inpth
      AND n.iprx    = @iprx
      AND n.dimn    = 0
      AND n.coregID IS NULL
      AND n.value  IS NOT NULL
    ORDER BY ls.stmtID, p.report, p.line, n.ddate
    OPTION (RECOMPILE);
END;
GO

/*
============================================================================
 Example call
============================================================================

EXEC dbo.usp_sec_get_full_filing @adsh = '0000950170-23-035122';
*/
