====== pears.PlacementAnalysis ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Analysis of placement by nominal code

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **PlacementAnalysisID** | char(20) | NOT NULL |  |  |
| PlacementID | char(20) | NOT NULL |  |  |
| NominalCode | char(12) | NOT NULL |  |  |
| Percentage | double | NULL |  |  |
| FeeAmount | double | NULL |  |  |
| CommissionPayable | double | NULL |  |  |
| PeriodAuthorised | integer | NULL |  | NULL if not yet authorised |
| CurrentlyAuthorised | smallint | NULL | 0 | Non-zero if this is a currently authorised record |
| WhenAuthorised | timestamp | NULL | current timestamp |  |
| StaffID | char(20) | NULL |  | Auto-entered staffid |
| PartCredit | smallint | NULL | 0 | Non-zero if this represents a part credit |

===== Primary Key =====
  * PlacementAnalysisID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Placement | PlacementID | [[database:tables:pears_placement|pears.Placement (placementid)]] | NOT NULL; ON DELETE CASCADE |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| PlacementAnalysis_Period | Index | PeriodAuthorised |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| PlacementAnalysisInsert | before | insert order 1 |
| PlacementAnalysisAuthorised | before | update of "CurrentlyAuthorised" order 1 |
| placementanalysisauditupdate | before | update of "NominalCode", "Percentage" order 2 |
| placementanalysisauditinsdel | before | insert,delete order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."PlacementAnalysis"
-- Table comment: Analysis of placement by nominal code
-- Statement count: 16

CREATE TABLE "pears"."PlacementAnalysis" (
    "PlacementAnalysisID"            char(20) NOT NULL
   ,"PlacementID"                    char(20) NOT NULL
   ,"NominalCode"                    char(12) NOT NULL
   ,"Percentage"                     double NULL
   ,"FeeAmount"                      double NULL
   ,"CommissionPayable"              double NULL
   ,"PeriodAuthorised"               integer NULL
   ,"CurrentlyAuthorised"            smallint NULL DEFAULT 0
   ,"WhenAuthorised"                 timestamp NULL DEFAULT current timestamp
   ,"StaffID"                        char(20) NULL
   ,"PartCredit"                     smallint NULL DEFAULT 0
   ,PRIMARY KEY ("PlacementAnalysisID" ASC) 
)
go


COMMENT ON COLUMN "pears"."PlacementAnalysis"."PeriodAuthorised" IS 
	'NULL if not yet authorised'
go


COMMENT ON COLUMN "pears"."PlacementAnalysis"."CurrentlyAuthorised" IS 
	'Non-zero if this is a currently authorised record'
go


COMMENT ON COLUMN "pears"."PlacementAnalysis"."StaffID" IS 
	'Auto-entered staffid'
go


COMMENT ON COLUMN "pears"."PlacementAnalysis"."PartCredit" IS 
	'Non-zero if this represents a part credit'
go


COMMENT ON TABLE "pears"."PlacementAnalysis" IS 
	'Analysis of placement by nominal code'
go


ALTER TABLE "pears"."PlacementAnalysis"
    ADD NOT NULL FOREIGN KEY "Placement" ("PlacementID" ASC)
    REFERENCES "pears"."Placement" ("placementid")
    ON DELETE CASCADE
go


CREATE INDEX "PlacementAnalysis_Period" ON "pears"."PlacementAnalysis"
    ( "PeriodAuthorised" )
go


create trigger "PlacementAnalysisInsert" before insert order 1 on
"pears"."PlacementAnalysis"
referencing new as "new_PlacementAnalysis"
for each row
when("new_PlacementAnalysis"."staffid" is null)
begin
  set "new_PlacementAnalysis"."staffid" = "userstaffid"
exception
  when others then
    set "new_PlacementAnalysis"."staffid" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."PlacementAnalysis"."PlacementAnalysisInsert" IS 
{create trigger PlacementAnalysisInsert 
before insert order 1 on
pears.PlacementAnalysis
referencing new as new_PlacementAnalysis
for each row
when(new_PlacementAnalysis.staffid is null)
begin
  set new_PlacementAnalysis.staffid=userstaffid
exception
  when others then
    set new_PlacementAnalysis.staffid=null
end
}
go


create trigger "PlacementAnalysisAuthorised" before update of "CurrentlyAuthorised"
order 1 on "pears"."PlacementAnalysis"
referencing new as "NewPlaceAnal"
for each row
when("NewPlaceAnal"."CurrentlyAuthorised" = 1)
begin
  set "NewPlaceAnal"."WhenAuthorised" = current timestamp;
  set "NewPlaceAnal"."staffid" = "userstaffid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."PlacementAnalysis"."PlacementAnalysisAuthorised" IS 
{create trigger PlacementAnalysisAuthorised 
 before update of CurrentlyAuthorised
order 1 on pears.PlacementAnalysis
referencing new as NewPlaceAnal
for each row
when(NewPlaceAnal.CurrentlyAuthorised = 1)
begin
  set NewPlaceAnal.WhenAuthorised=current timestamp;
  set NewPlaceAnal.staffid=userstaffid;
end
}
go


create trigger "placementanalysisauditupdate" before update of "NominalCode",
"Percentage" order 2 on "pears"."PlacementAnalysis"
referencing old as "old_pa" new as "new_pa"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Placement' and "AuditFlag" = 1 and "audititemid" = 'XXMAN0000000000015'))
begin
  declare "OurRef" char(20);
  if(select "temp" from "placement" where "placementid" = "old_pa"."placementid") = 1 then
    select "refcode" into "ourref" from "placement" where "placementid" = "old_pa"."placementid";
    if update("NominalCode") then call "AuditLog"('PLACEMENT',"new_pa"."placementid","string"('NominalCode Updated Our Ref. - ',"ourref"),"old_pa"."NominalCode","new_pa"."NominalCode")
    end if;
    if update("Percentage") and "round"("old_pa"."Percentage",2) <> "round"("new_pa"."Percentage",2) then call "AuditLog"('PLACEMENT',"new_pa"."placementid","string"('Percentage Updated Our Ref. - ',"ourref"),"round"("old_pa"."Percentage",2),"round"("new_pa"."Percentage",2))
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."PlacementAnalysis"."placementanalysisauditupdate" IS 
{create trigger placementanalysisauditupdate 
 before update of NominalCode,
Percentage order 2 on pears.PlacementAnalysis
referencing old as old_pa new as new_pa
for each row
when(exists(select * from AuditItems where AreaName = 'Placement' and AuditFlag = 1 and audititemid = 'XXMAN0000000000015'))
begin
  declare OurRef char(20);
  if(select temp from placement where placementid = old_pa.placementid) = 1 then
    select refcode into ourref from placement where placementid = old_pa.placementid;
    if update(NominalCode) then call AuditLog('PLACEMENT',new_pa.placementid,string('NominalCode Updated Our Ref. - ',ourref),old_pa.NominalCode,new_pa.NominalCode)
    end if;
    if update(Percentage) and round(old_pa.Percentage,2) <> round(new_pa.Percentage,2) then call AuditLog('PLACEMENT',new_pa.placementid,string('Percentage Updated Our Ref. - ',ourref),round(old_pa.Percentage,2),round(new_pa.Percentage,2))
    end if
  end if
end
}
go


create trigger "placementanalysisauditinsdel" before insert,delete order 1 on
"pears"."PlacementAnalysis"
referencing old as "old_pa" new as "new_pa"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Placement' and "AuditFlag" = 1 and "audititemid" = 'XXMAN0000000000015'))
begin
  declare "OurRef" char(20);
  if inserting then
    if(select "temp" from "placement" where "placementid" = "new_pa"."placementid") = 1 then
      select "refcode" into "ourref" from "placement" where "placementid" = "new_pa"."placementid";
      call "AuditLog"('PLACEMENT',"new_pa"."placementid","string"('Split Inserted Our Ref. - ',"ourref"),'',"new_pa"."NominalCode" || ' ' || "round"("new_pa"."Percentage",2))
    end if end if;
  if deleting then
    if(select "temp" from "placement" where "placementid" = "old_pa"."placementid") = 1 then
      select "refcode" into "ourref" from "placement" where "placementid" = "old_pa"."placementid";
      call "AuditLog"('PLACEMENT',"old_pa"."placementid","string"('Split Deleted Our Ref. - ',"ourref"),"old_pa"."NominalCode" || ' ' || "round"("old_pa"."Percentage",2),'')
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."PlacementAnalysis"."placementanalysisauditinsdel" IS 
{create trigger placementanalysisauditinsdel 
 before insert,delete order 1 on
pears.PlacementAnalysis
referencing old as old_pa new as new_pa
for each row
when(exists(select * from AuditItems where AreaName = 'Placement' and AuditFlag = 1 and audititemid = 'XXMAN0000000000015'))
begin
  declare OurRef char(20);
  if inserting then
    if(select temp from placement where placementid = new_pa.placementid) = 1 then
      select refcode into ourref from placement where placementid = new_pa.placementid;
      call AuditLog('PLACEMENT',new_pa.placementid,string('Split Inserted Our Ref. - ',ourref),'',new_pa.NominalCode || ' ' || round(new_pa.Percentage,2))
    end if
  end if;
  if deleting then
    if(select temp from placement where placementid = old_pa.placementid) = 1 then
      select refcode into ourref from placement where placementid = old_pa.placementid;
      call AuditLog('PLACEMENT',old_pa.placementid,string('Split Deleted Our Ref. - ',ourref),old_pa.NominalCode || ' ' || round(old_pa.Percentage,2),'')
    end if
  end if
end
}
go
</code>