-- ABS-62308 / ABS-66982
------------------------------------CONTRACT----------------------------------

ALTER TABLE CONTRACT ADD (
		PURCHASEORDERTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT PURCHASEORDERTYPE_VALID1_1 CHECK (PURCHASEORDERTYPE IN (0,1,2,3,4)));
		
COMMENT ON COLUMN CONTRACT.PURCHASEORDERTYPE IS '0 = Invoice (default)
1 = Product
2 = Product and Service (USPS)
3 = Invoice and Line Type
4 = Product and Line Type
';

create or replace procedure tempCreate_PURCHASEORDERTYPE is
  -- If records exist for the contract in ContractPOInvoiceLineType and ContractPurchaseOrderLine then PurchaseOrderType = 4
  cursor CPOInvoiceLineTypeAndCPOLine is
    select c.contract_id
      from contract                  c,
           ContractPOInvoiceLineType cpt,
           ContractPurchaseOrderLine cpol,
           contractpurchaseorder     cpo
     where c.contract_id = cpo.contract_id
       and cpo.contractpurchaseorder_id = cpol.contractpurchaseorder_id
       and cpt.contractpurchaseorder_id = cpol.contractpurchaseorder_id;

  --If records exist for the contract in ContractPOInvoiceLineType and not ContractPurchaseOrderLine then PurchaseOrderType = 3
  cursor CPOInvLineTypWithoutCPOLine is
    select c.contract_id
      from ContractPOInvoiceLineType cpot,
           contract                  c,
           contractpurchaseorder     cpo
      left outer join ContractPurchaseOrderLine cpol
        on cpol.contractpurchaseorder_id = cpo.contractpurchaseorder_id
     where cpot.contractpurchaseorder_id = cpo.contractpurchaseorder_id
       and cpo.contract_id = c.contract_id;

  --  If records exist for the contract in ContractPurchaseOrderLine and not ContractPOInvoiceLineType then PurchaseOrderType = 1
  cursor CPOLineWithoutCPOInvLineType is
    select c.contract_id
      from contractpurchaseorderline cpol,
           contract                  c,
           contractpurchaseorder     cpo
      left outer join contractpoinvoicelinetype cpot
        on cpot.contractpurchaseorder_id = cpo.contractpurchaseorder_id
     where cpol.contractpurchaseorder_id = cpo.contractpurchaseorder_id
       and cpo.contract_id = c.contract_id;
begin
  for x in CPOInvoiceLineTypeAndCPOLine loop
    begin
      update contract c1
         set c1.purchaseordertype = 4
       where c1.contract_id = x.contract_id;
    exception
      when others then
        rollback;
    end;
    commit;
  end loop;

  for y in CPOInvLineTypWithoutCPOLine loop
    begin
      update contract c1
         set c1.purchaseordertype = 3
       where c1.contract_id = y.contract_id;
    exception
      when others then
        rollback;
    end;
    commit;
  end loop;

  for z in CPOLineWithoutCPOInvLineType loop
    begin
      update contract c1
         set c1.purchaseordertype = 1
       where c1.contract_id = z.contract_id;
    exception
      when others then
        rollback;
    end;
    commit;
  end loop;
end tempCreate_PURCHASEORDERTYPE;
/
execute tempCreate_PURCHASEORDERTYPE;
drop procedure tempCreate_PURCHASEORDERTYPE;